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

user_14059 0
Viewed 139290

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.

greatest-n-per-group postgresql sql
6 Answers

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
user_1101640

Something like this:

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

should do it.

user_969630
Related