SQL query to get most recent row for each instance of a given key

Question

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this:

username      |  ip      |  time_stamp  
--------------|----------|--------------  
ted           | 1.2.3.4  | 10  
jerry         | 5.6.6.7  | 12  
ted           | 8.8.8.8  | 30

I'd expect the output of the query to be:

jerry    |  5.6.6.7   |  12
ted      |  8.8.8.8   |  30

Can I do this in a single sql query? In case it matters, the DBMS is Postgresql.

score 96 · Accepted Answer · answered Jul 16 '09 at 20:26

96

Try this:

Select u.[username]
      ,u.[ip]
      ,q.[time_stamp]
From [users] As u
Inner Join (
    Select [username]
          ,max(time_stamp) as [time_stamp]
    From [users]
    Group By [username]) As [q]
On u.username = q.username
And u.time_stamp = q.time_stamp

answered Jul 16 '09 at 20:26

Chris Nielsen

14,731
7
48
54

I was on the right track but I couldn't quite get the join right. This did the trick. Thanks! – alanc10n Jul 16 '09 at 20:31
2

Does this work in SQL Server? Tried the exact same thing on similar data but I'm getting a row for every "ip" along with only the most recent timestamp. – tcash21 Oct 01 '12 at 23:12
Used against a HANA table, this produced multiple rows where 1 was expected. HANA has a lot of things that are not the same as most of the popular SQL engines. – J E Carter II Sep 15 '16 at 20:30

score 33 · Answer 2 · answered Dec 24 '14 at 00:51

33

Nice elegant solution with ROW_NUMBER window function (supported by PostgreSQL - see in SQL Fiddle):

SELECT username, ip, time_stamp FROM (
 SELECT username, ip, time_stamp, 
  ROW_NUMBER() OVER (PARTITION BY username ORDER BY time_stamp DESC) rn
 FROM Users
) tmp WHERE rn = 1;

answered Dec 24 '14 at 00:51

Cristian Scutaru

1,375
19
23

3

This is the best answer, as it doesn't involve a join on a nested query. Only problem is it needs to include the [ip] in the key as well as the username per the question. – dashnick Mar 02 '16 at 17:57
This is the most performant solution. Tried these solutions using POSTGRES explain analyse tool and this one is the best. Excellent code – Martín Zaragoza Sep 24 '21 at 14:23

score 8 · Answer 3 · answered Jul 16 '09 at 20:28

8

Something like this:

select * 
from User U1
where time_stamp = (
  select max(time_stamp) 
  from User 
  where username = U1.username)

should do it.

answered Jul 16 '09 at 20:28

Kim Gräsman

7,438
1
28
41

2

That would work if `time_stamp` would be unique and you cannot assume that. – Nux Jun 29 '16 at 15:08

score 3 · Answer 4 · answered Aug 13 '13 at 09:50

3

Both of the above answers assume that you only have one row for each user and time_stamp. Depending on the application and the granularity of your time_stamp this may not be a valid assumption. If you need to deal with ties of time_stamp for a given user, you'd need to extend one of the answers given above.

To write this in one query would require another nested sub-query - things will start getting more messy and performance may suffer.

I would have loved to have added this as a comment but I don't yet have 50 reputation so sorry for posting as a new answer!

answered Aug 13 '13 at 09:50

user2323503

59
7

5

"Both of the above..." isn't a good way to start an answer. How am I to know if the answers above this are the ones you are referring to? Answers can appear in a different order based on their score. – Bryan Oakley Dec 11 '14 at 16:58
7

Rob, I don't think you're telling him something he doesn't know. He COULDN'T comment and the answers he was referring to were obviously flawed. What's more important, spreading knowledge or critiquing where the text was placed? – Chris Ruskai Oct 05 '15 at 19:03

err1 · Answer 5 · 2015-02-05T10:31:19.750

2

Can't post comments yet, but @Cristi S's answer works a treat for me.

In my scenario, I needed to keep only the most recent 3 records in Lowest_Offers for all product_ids.

Need to rework his SQL to delete - thought that this would be ok, but syntax is wrong.

DELETE from (
SELECT product_id, id, date_checked,
  ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date_checked DESC) rn
FROM lowest_offers
) tmp WHERE > 3;

edited Feb 05 '15 at 10:31

answered Feb 05 '15 at 09:30

err1

499
9
22

Corrected SQL: DELETE FROM Lowest_Offer WHERE id in ( SELECT id FROM ( SELECT product_id, id, date_checked, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date_checked DESC) rn FROM Lowest_Offer ) tmp WHERE rn>3 ) – err1 Feb 05 '15 at 10:34
i think this requires an desc index over timestamp – PirateApp Jun 12 '18 at 03:44

score 0 · Answer 6 · answered Sep 05 '13 at 15:32

I've been using this because I'm returning results from another table. Though I'm trying to avoid the nested join if it helps w/ one less step. Oh well. It returns the same thing.

select
users.userid
, lastIP.IP
, lastIP.maxdate

from users

inner join (
    select userid, IP, datetime
    from IPAddresses
    inner join (
        select userid, max(datetime) as maxdate
        from IPAddresses
        group by userid
        ) maxIP on IPAddresses.datetime = maxIP.maxdate and IPAddresses.userid = maxIP.userid
    ) as lastIP on users.userid = lastIP.userid

I think this is the answer for above user's response – jawz101 Sep 05 '13 at 15:33 — jawz101, Sep 05 '13 at 15:33

SQL query to get most recent row for each instance of a given key

6 Answers6

Linked