I have a messages table with fields channel_number, body, created_at, recipeint_id & sender_id I want to have messages distinct on channel_number and order by messages.created_at
basically I want to show all channels in paginations and channels with latest message will be at top. This is what I’ve tried:
SELECT * FROM messages where id IN ( (SELECT channel_number, MIN(id) AS messages_id FROM messages GROUP BY channel_number ) as a ) a.messages_id ORDER BY a.messages_id DESC;
I have been working on this for a couple of hours and made many queries but it didn’t work because in results I need all fields of message instead of selected channel and id.
Edits – I am using postgres and there is no uniq constrain on channle_number in messages table
This worked good for me
SELECT * FROM messages where (channel_number, id) IN (select channel_number, MIN(id) AS last_message_id FROM "messages" WHERE (sender_id = 3 OR recipient_id =2) ) GROUP BY "messages"."channel_number" ORDER BY created_at DESC