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.