Create a SQL query to retrieve most recent records

I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows:

Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/8/2009 8:00am B.Sisko In 1/7/2009 5:00pm B.Sisko In 1/7/2009 8:00am B.Sisko In 1/7/2009 8:00am K.Janeway In 1/5/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation 

I would like to query the data and return the most recent status for each user, in this case, my query would return the following results:

Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/7/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation 

I am try to figure out the TRANSACT-SQL to make this happen? Any help would be appreciated.

5 Answers

Aggregate in a subquery derived table and then join to it.

 Select Date, User, Status, Notes from [SOMETABLE] inner join ( Select max(Date) as LatestDate, [User] from [SOMETABLE] Group by User ) SubMax on [SOMETABLE].Date = SubMax.LatestDate and [SOMETABLE].User = SubMax.User 

another way, this will scan the table only once instead of twice if you use a subquery

only sql server 2005 and up

select Date, User, Status, Notes from ( select m.*, row_number() over (partition by user order by Date desc) as rn from [SOMETABLE] m ) m2 where m2.rn = 1; 

The derived table would work, but if this is SQL 2005, a CTE and ROW_NUMBER might be cleaner:

WITH UserStatus (User, Date, Status, Notes, Ord) as ( SELECT Date, User, Status, Notes, ROW_NUMBER() OVER (PARTITION BY User ORDER BY Date DESC) FROM [SOMETABLE] ) SELECT User, Date, Status, Notes from UserStatus where Ord = 1 

This would also facilitate the display of the most recent x statuses from each user.

Another easy way:

SELECT Date, User, Status, Notes FROM Test_Most_Recent WHERE Date in ( SELECT MAX(Date) from Test_Most_Recent group by User) 

Add an auto incrementing Primary Key to each record, for example, UserStatusId.

Then your query could look like this:

select * from UserStatus where UserStatusId in ( select max(UserStatusId) from UserStatus group by User ) 

Date User Status Notes