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

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

Asked on January 10, 2019 in PostgreSQL.
Add Comment


  • 3 Answer(s)

    Try this QUERY

    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
    
    Answered on January 10, 2019.
    Add Comment

    Nice delicate answer with ROW_NUMBER window function (supported by PostgreSQL

    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;
    
    Answered on January 10, 2019.
    Add Comment

    Try this query

    select *
    from User U1
    where time_stamp = (
       select max(time_stamp)
       from User
       where username = U1.username)
    
    Answered on January 10, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.