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.
This question is related to
sql
tsql
derived-table
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)
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;
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
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.
Source: Stackoverflow.com