everyone! I have a table in postgres 'chat'
| id | channel_id | message | user_id | chat_created_date |
|---|---|---|---|---|
| 1 | 4243 | hello | 23 | 2018-05-13 |
| 2 | 4243 | hello2 | 23 | 2018-05-11 |
| 3 | 4242 | hello | 23 | 2018-05-13 |
| 4 | 4242 | hello2 | 23 | 2018-05-11 |
I need to create query to get last message by unique channel_id with user_id
The result should be like this
| channel_id | message | chat_created_date |
|---|---|---|
| 4243 | hello | 2018-05-13 |
| 4242 | hello | 2018-05-13 |
CodePudding user response：
This can be done efficiently using the proprietary distinct on () in Postgres
select distinct on (channel_id) channel_id, message, chat_created_date
from chats
order by channel_id, chat_created_date desc