0

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
Improve this question
1
  • Stackoveflow for programming questions Commented May 15, 2010 at 13:03

1 Answer 1

Reset to default
0

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
Improve this answer
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 Commented May 15, 2010 at 12:45

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.