Дано: таблица chat_messages
:
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+----------------------------------------------
id | integer | | not null | nextval('chat_chatmessage_id_seq'::regclass)
created_at | timestamp with time zone | | not null |
updated_at | timestamp with time zone | | not null |
message | character varying(4095) | | |
recipient_id | integer | | not null |
sender_id | integer | | not null |
Путем экспериментов я что-то типа такого получил:
# select * from chat_messages where id in (select max(id) from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id));
id | created_at | updated_at | message | recipient_id | sender_id
----+-------------------------------+------------------------------+------------+--------------+-----------
21 | 2021-04-13 01:52:28.311547+00 | 2021-04-13 01:52:28.31161+00 | Это ты кал | 50 | 46
(1 row)
Но есть ли более красивое решение?