Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
Stackoveflow for programming questions – gbn May 15 '10 at 13:03
up vote 0 down vote accepted

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
share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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