-1

I have the following table

create table messages(
    id         bigserial primary key,
    created_at timestamp with time zone,
    message    text,
    from_id    bigint,
    to_id      bigint
);

I'm select all unique conversations by using the following query

SELECT DISTINCT ON (LEAST(from_id, to_id), GREATEST(from_id, to_id))
       id, message, created_at
FROM   messages
ORDER  BY LEAST(from_id, to_id), GREATEST(from_id, to_id), created_at DESC;

But I'd like to change it so I get the last message from each conversation. Not the first. Any idea how I can do that? Possible using a different query?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user672009
  • 4,379
  • 8
  • 44
  • 77
  • 1
    [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT) shows example very close to yours query and says that in this case you get last row for each pair. Because you sorted rows by created_at desc. And this [dbfiddle](https://dbfiddle.uk/IOs83QBl) confirms that. – Ponder Stibbons Jun 18 '23 at 19:39
  • @nbk - yes, it does [dbfiddle](https://dbfiddle.uk/kqPavX2l). You inserted pair (10, 201) which adds new row. Everything looks OK. – Ponder Stibbons Jun 18 '23 at 20:06
  • The same pair `(from_id, to_id)` would normally be allowed to have many distinct conversations (with 1-n messages). But your table seems seems to lump all messages between the two together. So you are actually looking for the absolute latest message between each pair? – Erwin Brandstetter Jun 18 '23 at 23:30

1 Answers1

0

If created_at is defined NOT NULL (and you are just not showing it), then your query already does what you ask for.

See:

If there are many messages per pair, then different query styles will perform (much) better.

For only few, DISTINCT ON is just right.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228