Question 191037 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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;
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search