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.

CodePudding user response：

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;

CodePudding user response：

Besides Tims's ansser,it seems GROUP BY can also do it

SELECT t1.* FROM youtable t1
JOIN
 (SELECT MIN(timestamp) as minTimestamp,User FROM yourtable GROUP BY User) t2
ON t1.User=t2.User AND t1.timestamp = t2.minTimestamp
ORDER BY t1.User

CodePudding user response：

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

select distinct on ("User") *
from the_table
order by "User", "timestamp" desc;