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

sql postgresql greatest-n-per-group
alanc10n picture alanc10n · Jul 16, 2009 · Viewed 138.3k times · Source

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.

Answer

Chris Nielsen picture Chris Nielsen · Jul 16, 2009

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to select id with max date group by category in PostgreSQL?

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6 id category date 1 a 2013-01-01 2 b 2013-01-03 3 c 2013-01-02 4 a 2013-01-02 5 b 2013-01-02 6 c 2013-01-03 7 a 2013…

Read More
PostgreSQL MAX and GROUP BY

I have a table with id, year and count. I want to get the MAX(count) for each id and keep the year when it happens, so I make this query: SELECT id, year, MAX(count) FROM table GROUP BY …

Read More