CODEWITHSUNDEEP
sqlsql-servergroup-by
Rayshawn
Rayshawn

Reputation: 2617

Selecting Most recent records within a month?

Using the following query and results, I'm looking for the most recent entry within a month where the EmpId is unique. The query below does not give me the desired result.

SELECT M.Name, M.Score, MAX(M.TestDate) FROM myTable M
GROUP BY M.Name, M.Score, M.TestDate
ORDER BY M.TestDate


               myTable

EmpId      TestId   Score        Name   TestDate
1           1       MATH         90       4/1/2008
1           1       MATH         100      4/6/2008
1           1       MATH         70       4/15/2008
2           1       MATH         70       5/1/2008
2           1       MATH         80       5/6/2008
2           1       MATH         100      5/15/2008

Desired:

EmpId      TestId   Score        Name   TestDate
1           1       MATH         70       4/15/2008
2           1       MATH         100      5/15/2008

Upvotes: 0

Views: 63

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can do it using ROW_NUMBER() function inside sub-query:

SELECT * FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY TestDate DESC) RN
    FROM MyTable
) x
WHERE x.RN = 1

SQLFiddle DEMO

or.. if for some reason, you really want to use GROUP BY can be done like this:

WITH CTE_Group AS 
(
    SELECT EmpId, MAX(TestDate) AS MaxDate
    FROM MyTable
    GROUP BY EmpId
) 
SELECT m.* 
FROM CTE_Group g
LEFT JOIN dbo.MyTable m ON g.EmpId = m.EmpId AND g.MaxDate = m.TestDate

SQLFiddle DEMO

Difference is - if there are two rows with same date for same empID - GROUP BY would return them both, while ROW_NUMBER() would still return only one. Replacing ROW_NUMBER() with RANK() function would also return both rows.

Upvotes: 2

Related Questions