How to find groups with most recent messages?

Question

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;

Answer 1

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