Finding all rows with unique combination of two columns

Question

I have this table messages;

sender_id    recipient_id
1            2
1            3
1            3
2            1
3            1
2            3

I wish to select rows such that:

Either sender_id or receiver_id = current_user.id.
The other field should be unique.

I.e. I want to select unique from table where sender_id = 2 or recipient_id = 2 and I need this result:

sender_id    recipient_id
2            1
2            3

How to do it?
Why? Because I wish to build a facebook-like inbox in which sent and received messages are aggregated, and this query is the bottleneck so far.

I am using rails 3.2 and Postgres 9.3.

Erwin Brandstetter · Accepted Answer · 2019-02-28 16:34:51Z

7

SELECT sender_id AS user_id, recipient_id AS other_user_id
FROM   messages
WHERE  sender_id = $current_user_id

UNION
SELECT recipient_id, sender_id
FROM   messages
WHERE  recipient_id = $current_user_id
-- ORDER BY 1, 2  -- optional

UNION (not UNION ALL) removes duplicates from the result makingDISTINCT unnecessary. You might want to add ORDER BY at the end for sorted output.

Assuming a big table with relatively few qualifying rows, two btree indexes typically deliver best performance. One with leading or only sender_id, another one with leading or only recipient_id.

A single multicolumn index on (sender_id, receiver_id) or vice versa also works, but typically slower. See:

Is a composite index also good for queries on the first field?

edited Feb 28, 2019 at 16:34

answered Mar 29, 2014 at 12:14

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Zuhaib Ali Over a year ago

@Erwin how do I order these results to get the most recent first? The table also has an id column, and a created_at column both of which can be used for sorting... but how?

Erwin Brandstetter Over a year ago

@ZuhaibAli: First you would have to define which value to pick for each group of duplicates. The smallest id / created_at per group? I suggest you write a new question with the detailed requirements. You can always refer to this one for context.

Erwin Brandstetter Over a year ago

Note that I replaced my original answer with a better one.

Piroinno · Accepted Answer · 2014-03-29 17:28:21Z

3

With ANSI SQL:

SELECT DISTINCT sender_id, reciepient_id
FROM messages
WHERE (sender_id = current_user.id or reciepient_id = current_user.id)

edited Mar 29, 2014 at 17:28

answered Mar 29, 2014 at 9:03

Piroinno

1151 silver badge9 bronze badges

3 Comments

Craig Ringer Over a year ago

If you have independent indexes on both sender_id and receiver_id (not a composite index on both together), PostgreSQL should use a bitmap index scan to find matching rows, too.

Erwin Brandstetter Over a year ago

This fails to remove duplicates.

Zuhaib Ali Over a year ago

Yes, this won't remove (1,2) vs (2,1) duplicates

Collectives™ on Stack Overflow

Finding all rows with unique combination of two columns

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related