[sql] 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.

I've been using this because I'm returning results from another table. Though I'm trying to avoid the nested join if it helps w/ one less step. Oh well. It returns the same thing.

select
users.userid
, lastIP.IP
, lastIP.maxdate

from users

inner join (
    select userid, IP, datetime
    from IPAddresses
    inner join (
        select userid, max(datetime) as maxdate
        from IPAddresses
        group by userid
        ) maxIP on IPAddresses.datetime = maxIP.maxdate and IPAddresses.userid = maxIP.userid
    ) as lastIP on users.userid = lastIP.userid

Can't post comments yet, but @Cristi S's answer works a treat for me.

In my scenario, I needed to keep only the most recent 3 records in Lowest_Offers for all product_ids.

Need to rework his SQL to delete - thought that this would be ok, but syntax is wrong.

DELETE from (
SELECT product_id, id, date_checked,
  ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date_checked DESC) rn
FROM lowest_offers
) tmp WHERE > 3;

Both of the above answers assume that you only have one row for each user and time_stamp. Depending on the application and the granularity of your time_stamp this may not be a valid assumption. If you need to deal with ties of time_stamp for a given user, you'd need to extend one of the answers given above.

To write this in one query would require another nested sub-query - things will start getting more messy and performance may suffer.

I would have loved to have added this as a comment but I don't yet have 50 reputation so sorry for posting as a new answer!

Something like this:

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

should do it.

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;