SQL query to get most recent row for each instance of a given key
Try this:
Select u.[username] ,u.[ip] ,q.[time_stamp]From [users] As uInner Join ( Select [username] ,max(time_stamp) as [time_stamp] From [users] Group By [username]) As [q]On u.username = q.usernameAnd u.time_stamp = q.time_stamp
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;
Something like this:
select * from User U1where time_stamp = ( select max(time_stamp) from User where username = U1.username)should do it.