Have the following messages table:
| ID | OWNER | TO | MESSAGE | DATE |
|---|---|---|---|---|
| 1 | y | x | Hello | 1 |
| 2 | y | x | World | 2 |
| 3 | z | x | Postgres | 3 |
Need to get data as conversations for TO, but only one message sorted by date desc, so the result is two columns of ids 2 and 3
select
m.id,
m.message,
m.date,
u.id as from_id,
u.username as from_username,
from messages as m
join users as u on u.id = m.owner
where m.to = x
group by m.owner
order by m.created_at desc
1