Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

1 Answer

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 * 
from
(
  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
add comment

Your Answer

 
discard

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.