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.
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.