Select MAX dates plus ID value

问题:

Please consider the following table...

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

...containing the following values:

INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (1, 1, 50, '2010-10-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (2, 1, 50, '2010-11-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (3, 1, 50, '2010-12-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (4, 2, 20, '2010-10-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (5, 2, 30, '2010-11-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (6, 2, 20, '2012-11-01')

I need to retrieve the maximum date for each userID/testID combination of values, and also the accompanying ID value. The results should be:

ID   userID  testID  someDate
-------------------------------
3    1       50      2010-12-01
5    2       30      2010-11-01
6    2       20      2012-11-01

When I try the following query, the result set becomes incorrect and all rows are shown. I cannot omit ID from the GROUP BY clause because it causes and error. Can anyone help please? It seems long-winded to join the table to itself to get these values.

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

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

回答1:

Please try:

select * from (
    select 
        *, 
        ROW_NUMBER() over (partition by userID, testID order by SomeDate desc) Rnum 
    From @tmp
)x where Rnum=1