CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
EvilDr
EvilDr

Reputation: 9610

Select top dates grouped by ID's

I have a table as follows:

DECLARE @tmp TABLE
    (
    userID int,
    testID int,
    someDate datetime
    )

Within it I store dates along with two ID values, e.g.

INSERT INTO @tmp (userID, testID, someDate) VALUES (1, 50, '2010-10-01')
INSERT INTO @tmp (userID, testID, someDate) VALUES (1, 50, '2010-11-01')
INSERT INTO @tmp (userID, testID, someDate) VALUES (1, 50, '2010-12-01')
INSERT INTO @tmp (userID, testID, someDate) VALUES (2, 20, '2010-10-01')
INSERT INTO @tmp (userID, testID, someDate) VALUES (2, 20, '2010-11-01')

I need to select the latest date per userID/testID combination. So, the result would be

userID  testID  someDate
1       50      2010-12-01
2       20      2010-11-01

It sounds really easy but I can't figure it out. SQL Fiddle Here.

Upvotes: 1

Views: 126

Answers (3)

Darren
Darren

Reputation: 70786

SELECT userId, testId, MAX(someDate)
FROM @tmp
GROUP BY testId, userId

http://www.sqlfiddle.com/#!6/d41d8/5205

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

SELECT userID, testID, MAX(someDate)
FROM @tmp
GROUP BY testId,userID;

fiddle

Upvotes: 2

T I
T I

Reputation: 9943

Try

SELECT t1.* FROM @tmp t1
INNER JOIN (SELECT userId, MAX(someDate) someDate
            FROM @tmp
            GROUP BY userId) t2
        ON t1.userId = t2.userId
       AND t1.someDate = t2.someDate

Upvotes: 0

Related Questions