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:

  • Streaming directly to a database
  • Return an output parameter from SQL Server via a stored procedure and c#
  • Convert bit type to Yes or No by query Sql Server 2005
  • What is File Table in SQL Server?
  • Convert hex string to binary SQL Server
  • T-SQL | Dynamic Transpose
  • 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

    One Solution collect form web for “Select MAX dates plus ID value”

    Please try:

    select * from (
        select 
            *, 
            ROW_NUMBER() over (partition by userID, testID order by SomeDate desc) Rnum 
        From @tmp
    )x where Rnum=1
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.