MAX() problem
-
I'm trying to use CTEs to get away from lookup type UDFs. I was told in another forum here that Group By with the MAX() function was the way to go. Most of the time it is. However, I'm having a problem with the following code which is supposed to return the employee ID, the most recent employment record by date for that employee and the employment code in that record:
SELECT MCID, MAX([Date]) AS [Date], SepCode
FROM Employment
GROUP BY MCID, SepCode
ORDER BY MCID
This is an example of the result:
MCID | Date | SepCode
1 | 10/31/2001 12:00:00 AM | 0
1 | 6/23/2002 12:00:00 AM | 40
2 | 8/12/1998 12:00:00 AM | 0
2 | 11/19/1998 12:00:00 AM | 34
3 | 6/12/2002 12:00:00 AM | 0
4 | 6/6/2001 12:00:00 AM | 0
4 | 9/6/2001 12:00:00 AM2 | 4
What I want to get is:
1 | 6/23/2002 12:00:00 AM | 40
2 | 11/19/1998 12:00:00 AM | 34
3 | 6/12/2002 12:00:00 AM | 0
4 | 9/6/2001 12:00:00 AM2 | 4
The code I'm trying to replace is:
SELECT TOP (1) MCID, Date, SepCode
FROM Employment
WHERE (MCID = @MCID)
ORDER BY Date DESC
Any ideas how I can modify the first code set to return only the most recent record for each employee?
Thanks.
Kato Wilbur
-
Untested, but this should work
WITH CTE AS
(SELECT MCID, Date, SepCode,
ROW_NUMBER() OVER(PARTITION BY MCID ORDER BY Date DESC) as rn
FROM Employment)
SELECT MCID, Date, SepCode
FROM CTE
WHERE rn=1
ORDER BY MCID
____________________________________________________
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
-
It certainly did.
Thanks a bunch.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply