How to select * from table but distinct on 1 column and order by on another column

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

Answer

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