0

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

GMB
  • 216,147
  • 25
  • 84
  • 135
NeverSleeps
  • 1,439
  • 1
  • 11
  • 39

3 Answers3

2

This can be done using the window function row_number() to assign an incrimented id for each record grouped by chat_id and ordered by created_at :

select *
from (
  select *, row_number() over (partition by chat_id order by created_at desc ) as rn
  from message
) as s
where rn = 1

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
2

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;
puchal
  • 1,883
  • 13
  • 25
0

I created your table guessing VARCHAR(255) for all the string values and TIMESTAMP for the date / time field. This seems to work.

select chat_id, max(created_at) from message group by chat_id;
Dan
  • 45
  • 4