SQL query to get most recent row for each instance of a given key

Question

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

User · Answer

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

User · Answer

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  gt  3

User · Answer

Something like this   select    from User U1 where time stamp       select max time stamp     from User    where username   U1 username    should do it

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL query to get most recent row for each instance of a given key

Examples related to sql

Examples related to postgresql

Examples related to greatest-n-per-group