SQL: How find newest row for each value of some column

Question

I want to find the newest message entry for each chat_id.

For example, for this table I need to get list of rows: with id=1c6bad12 for chat_id=ee5152ff and with id=e4485bdf for chat_id=29cc7217. (2th and 6th).

id	chat_id	created_at	text
1442a8e8	ee5152ff	2023-01-09 12:38:44.237280	hello1
1c6bad12	ee5152ff	2023-05-09 10:39:25.260409	hello3
514c2db5	29cc7217	2023-02-14 09:05:20.284902	qwerty1
c4247480	ee5152ff	2023-01-14 19:05:20.284902	hello2
e4485bdf	29cc7217	2023-06-14 11:05:20.284902	qwerty2

Now I have only such query:

SELECT * 
FROM message
ORDER BY created_at DESC, id DESC;

But it reruns all rows

Answer 1

Your query returns all rows because only what it does is ordering.

Since you want to return one row per chat_id you can use DISTINCT ON (Note that column that you want to use in DISTINCT ON must be as initial column in ORDER BY

SELECT DISTINCT ON (chat_id) *
FROM message
ORDER BY chat_id, created_at DESC;