Create a SQL query to retrieve most recent records

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Create a SQL query to retrieve most recent records

Examples related to sql

Examples related to tsql

Examples related to derived-table