Question 114232 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get last record of every user

So my table looks like this

PK1 | PK2 | User(not unique)| value 1 | value 2| timestamp when added

I want to get most recent values for value 1 and value 2 for every user. How would I achieve this?

Also, PKs are not unique on their own, but in combination.

Tags:

2

Answers


  1. 0

    We can use ROW_NUMBER() here:

    WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY PK1, PK2 ORDER BY timestamp DESC) rn
    FROM yourTable t
)

SELECT PK1, PK2, value1, value2
FROM cte
WHERE rn = 1;
    Login or Signup to reply.
  2. 0

    Another alternative is to use PostgreSQL’s proprietary distinct on ()

    select distinct on ("User") *
from the_table
order by "User", "timestamp" desc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search