I have data in a table (below), and I need to select the most recent update from each user. Here the data has been sorted by date, so the 'SomeData' column of the most recent unique value of each user. Top 1 SomeData isn't going to work because it will only return for one user. Is this even possible using only SQL?

Date        SomeData    User    ...
8/5/2010    2.2     UserC
4/5/2010    1.1     UserA
3/5/2010    9.4     UserB
1/5/2010    3.7     UserA
1/5/2010    6.1     UserB
  • Stackoveflow for programming questions – gbn May 15 '10 at 13:03

A quick hack - something like this should work assuming your source table name is 'd' you can use row_number() to order the results and then query the derived table:

select * 
  select Date, Data, [User], row_number() over(partition by [User] order by [Date] desc) rownumber
  from d
) x
where rownumber = 1
  • Thanks, works perfectly. Unfortunately, I'm actually using a CTE due to SQL Server 2005 and paging, so the final query is: ;WITH Results_CTE AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY TheDate ASC) AS RowNum FROM ( SELECT *, row_number() over(partition BY UserCode ORDER BY TheDate DESC) rownumber FROM Point points) x WHERE rownumber = 1 AND UserCode IN ('LGTB~7CW') ) SELECT * FROM Results_CTE WHERE RowNum > 0 – Echilon May 15 '10 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.