<t><br/>
WITH cte AS<br/>
(<br/>
SELECT *,<br/>
ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn<br/>
FROM DocumentStatusLogs<br/>
)<br/>
SELECT *<br/>
FROM cte<br/>
WHERE rn = 1<br/>
<br/><br/><br/>
If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead of ROW_NUMBER.<br/>
<br/>
As for normalised or not, it depends if you want to:<br/>
<br/>
- maintain status in 2 places<br/>
<br/>
- preserve status history<br/>
<br/>
- ...<br/>
<br/>
As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.</t>
