sqlt-sqlderived-table

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.


Solution

  • 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