Top Row from each Group
-
CheckTable
EmpID Date Amount
10 12/1/2011 100.00
10 12/1/2011 120.00
10 12/3/2011 175.00
10 12/9/2011 155.00
20 11/7/2011 100.00
20 11/9/2011 200.00
20 11/9/2011 175.00
30 12/2/2011 100.00
30 12/4/2011 150.00
I want to construct a query that will give me the last record by "date" for each employee. If the last rows consist of the same date, then I just need to return of the record. In the above exampe, EMPID 20 had 2 checks on 11/9/2011.
Desired Output
EmpID Date Amount
10 12/9/2011 155.00
20 11/9/2011 200.00
30 12/4/2011 150.00
Thanks
-
Use ROW_NUMBER
WITH CTE AS (SELECT EmpID,Date,Amount,
ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY Date DESC) AS rn
FROM CheckTable)
SELECT EmpID,Date,Amount
FROM CTE
WHERE rn=1
ORDER BY EmpID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
-
Thanks Mark - that worked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply