My Technical Notes

Tuesday, 2 October 2012

Getting Last 3 Statuses for each Project

Imagine you have a table of projects called Project and a table of project statuses called EntityStatus. Each project has zero or more project statuses. We want to get the last three statuses of each project.

Using ROW_NUMBER and PARTITION



-- table variable to store sample project ids
declare @proj_ids table(Id uniqueidentifier)

-- grab first ten project's id's which have a status
INSERT INTO @proj_ids (Id)
SELECT top 10 p.Id 
FROM Project p
WHERE (SELECT COUNT(*) FROM EntityStatus e WHERE e.EntityIdGuid = p.Id) > 0

SELECT * FROM 
(
 SELECT ROW_NUMBER() OVER(PARTITION BY EntityIdGuid ORDER BY StatusDate DESC) [ind], e1.*
 FROM EntityStatus e1 
 JOIN @proj_ids p_ids ON e1.EntityIdGuid = p_ids.Id
) e
WHERE [ind] <= 3

Instead of using a OVER PARTITION statement, we can instead use a CROSS APPLY:


SELECT ent.*
FROM @proj_ids p_ids
CROSS APPLY (SELECT TOP 3 * 
             FROM EntityStatus e 
             WHERE e.EntityIdGuid = p_ids.Id 
             ORDER BY e.StatusDate DESC) ent
order by id

On another note, if we wanted the Project ID to appear even if the project had no statuses against it, we would use the OUTER APPLY operator.

The last way is to use a cursor which loops through each id in proj_ids and then inserts records into a table variable, which is yet to be described.

No comments: