alanc10n alanc10n - 1 year ago 83
SQL Question

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 | | 10
jerry | | 12
ted | | 30

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

jerry | | 12
ted | | 30

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

Answer Source

Try this:

Select u.[username]
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download