I have two tables messages
and groups
messages
id, content, group_id, created_at
groups
id, created_at
I want to find the n
number of groups sorted by the most recent messages in them. How can I do that with PostgreSQL 14?
I'm running the following query but it's not giving the expected result.
SELECT *
FROM (
SELECT id, group_id, created_at, row_number() OVER (PARTITION BY group_id ) AS rn
FROM messages order by created_at desc
) sub;
CodePudding user response:
If I understand correctly, you can try to let ORDER BY created_at desc
in window function which might help you get highest row number per group_id
SELECT *
FROM (
SELECT *, row_number() OVER (PARTITION BY group_id order by created_at desc) AS rn
FROM messages
) sub
WHERE rn = 1