Selecting rows based on distinct 2 fields in postgres

Question

I have table messages

| id | user_id | recepient_id | message
| 1  |    1    |      2       | test1
| 2  |    1    |      2       | test2
| 3  |    2    |      1       | test3
| 4  |    3    |      1       | test4
| 5  |    3    |      2       | test5

I am user with id 1 and want to select rows where i am is a user_id or recepient_id (but only 1 row per user_id/recepient_id pair). It this example i want to select messages with ids - 2, 4 (or 1, 4 or 2, 3 or 2, 4. doesnt matter. just get uniq pairs for user_id/recepient_id)

How can i do it?

Clodoaldo Neto · Accepted Answer · 2012-06-21 22:45:21Z

1

select distinct on (least(user_id, recipient_id), greatest(user_id, recipient_id))
    id, user_id, recipient_id, message
from the_table
where user_id = 1 or recipient_id = 1

edited Jun 21, 2012 at 22:45

answered Jun 21, 2012 at 22:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting rows based on distinct 2 fields in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related