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 |
And users table:
ID | USERNAME |
---|---|
x | x |
y | y |
z | z |
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,
u.id as from_id,
u.username as from_username,
m.message,
m.date
from messages as m
join users as u on u.id = m.owner
where m.to = x
group by m.owner
order by m.date desc
Expected result:
ID | FROM_ID | FROM_USERNAME | MESSAGE | DATE |
---|---|---|---|---|
2 | y | y | World | 2 |
3 | z | z | Postgres | 3 |
2
Answers
Found solution by using select
distinct on (m.owner)
and removed group by and addedm.owner
inorder by
and worked as expected. So is as follows:Use ROW_NUMBER to get the latest message per owner
As per the error
It reflects the general rule: the columns listed in SELECT should appear in GROUP BY. If they don’t appear in GROUP BY, then they have to be used in the aggregate function. One case is excluded if primary key is included in the group by clause there is no need to add the remaining columns on the select list because primary key uniquely identifies every row on the table.