Filtering out unique rows in MySQL
Posted
by
jpatokal
on Stack Overflow
See other posts from Stack Overflow
or by jpatokal
Published on 2011-02-09T07:06:41Z
Indexed on
2011/02/09
7:25 UTC
Read the original article
Hit count: 188
So I've got a large amount of SQL data that looks basically like this:
user | src | dst
1 | 1 | 1
1 | 1 | 1
1 | 1 | 2
1 | 1 | 2
2 | 1 | 1
2 | 1 | 3
I want to filter out pairs of (src,dst) that are unique to one user (even if that user has duplicates), leaving behind only those pairs belonging to more than one user:
user | src | dst
1 | 1 | 1
1 | 1 | 1
2 | 1 | 1
In other words, pair (1,2) is unique to user 1 and pair (1,3) to user 2, so they're dropped, leaving behind only all instances of pair (1,1).
Any ideas? The answers to the question below can find the non-unique pairs, but my SQL-fu doesn't suffice to handle the complication of requiring that they belong to multiple users as well.
© Stack Overflow or respective owner