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.

score 94 · Accepted Answer · edited May 16 '10 at 02:56

94

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

edited May 16 '10 at 02:56

Michael Buen

38,643
9
94
118

answered Jun 26 '09 at 15:23

cmsjr

56,771
11
70
62

5

This is called a derived table. – SurroundedByFish Jun 26 '09 at 16:56
8

Be careful if the derived table ends up with duplicates for {LatestDate,User} – alphadogg Nov 07 '13 at 14:11
2

Never rely on uniqueness unless the database guarantees it; i would never recommend this approach over the answer by SQLMenace, as accidental duplicates in the result can cause quite unexpected downstream behavior. (best example of failure here is a scripted update which causes a couple of state changes to land at the same time) – Andrew Hill Jul 15 '15 at 02:46

score 87 · Answer 2 · edited Sep 12 '16 at 04:20

87

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;

edited Sep 12 '16 at 04:20

Pure.Krome

84,693
113
396
647

answered Jun 26 '09 at 15:41

SQLMenace

132,095
25
206
225

Magic, I was able to solve tricky query involving joining 3 tables and selecting only latest entry for each type based on this code. Thank you! – Sopuli Oct 29 '13 at 04:22
2

This has the same cost in execution plan as the accepted answer but when you need to group by multiple columns for example orgID,Month,Year, this answer is semantically cleaner. – gooddadmike Oct 10 '14 at 19:43
this randomly selects the record out of all records equal first according to the ordering. – Andrew Hill Jul 15 '15 at 02:47
@AndrewHill Maybe your comment was before `(partiation by my_pk order by event_time desc)`? This does what OP asked, but allows sort criteria to be on non-join column, which MAX() with GROUP BY does not. – yzorg Jul 29 '21 at 19:58

score 12 · Answer 3 · edited May 16 '10 at 03:22

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.

score 8 · Answer 4 · edited Sep 16 '15 at 15:03

8

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)

edited Sep 16 '15 at 15:03

wpercy

9,636
4
33
45

answered Jul 12 '12 at 07:20

Mahesh RG

89
1
2

3

Won't this query go wrong if two users update their status at the same time, but that time isn't the latest activity for one of them? – SooDesuNe Jan 15 '13 at 03:50
Mahesh, you need to update your answer taking into account @SooDesuNe's comment. – Sunny R Gupta Feb 27 '14 at 13:08
Dont use SELECTS in WHERE clauses if you can help it. Unless its a one time thing you need to look up. If this is going in a stored proc, it greatly increases the time to return results. Especially on large tables. – Argyle Ghost Jan 21 '16 at 21:16

score 1 · Answer 5 · answered Dec 20 '20 at 04:21

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

Create a SQL query to retrieve most recent records

5 Answers5

Linked

Related