Select distinct combinations of rows I try to select distinct combinations of rows (author_id, recipient_id), preserving following order and limit. Like on Facebook you have a list of recent conversations with given users. For example I'm a user with id=665 and I need a list of 20 recent conversations, 20 last distinct people a user with id=665 has talked. My current code:

select * from (
  select distinct on (author_id, recipient_id) * from private_message where author_id=665 or recipient_id=665
) p_m
order by id desc
limit 20;




In this case everything is ok besides that I want to keep bigger id (more recent), so message with id=10 needs to be drop, because it's duplicate of id=18.

So the expected result is:
If I get it right you would like the last row for each column combination (regardless of column order). Easiest is probably to enumerate the rows (using least, GREATEST for column order) in desc order and choose the first one

SELECT ... FROM (
    SELECT...
           , row_number() over (partition by least(author_id, recipient_id)
                                           , greatest(author_id, recipient_id) 
                                order by updated_at desc) as rn
    FROM private_message
    WHERE 665 IN (author_id, recipient_id) 
) as t
WHERE rn = 1
LIMIT 20