SQL query to get most recent row for each instance of a given key
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.
Solution 1:
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
Solution 2:
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;
Solution 3:
Something like this:
select *
from User U1
where time_stamp = (
select max(time_stamp)
from User
where username = U1.username)
should do it.