Selecting distinct pairs in MySQL

Question

I would like to select the rows that has the same values across column A and column B.

For example if my table is:

-----
A | B
-----
1 | 2
3 | 4
1 | 2
4 | 5

The output should be:

A  B
1  2

[A] SELECT DISTINCT A, B FROM table;
Selects all the values in the table.
[B] SELECT DISTINCT (A, B) FROM table;
Tells me that Distinct function can take only one value.
[C] SELECT A, B FROM table GROUP BY A, B;
Selects all the values in the table (Similar to A).

The question is similar to Selecting Distinct combinations., but the answer suggested there doesn't work.

Your question is unclear. Can you give an example of what data you have and what result you are looking for? — Mark Byers, Aug 17 '12 at 22:20
Do you want to select only the rows that have duplicates across `A` and `B` ? — Zane Bien, Aug 17 '12 at 22:24

Zane Bien · Accepted Answer · 2012-08-17T22:35:43.367

15

You want only the rows which have duplicates. You can use the HAVING clause to filter "groupings" of data based on aggregation functions:

SELECT   A,B
FROM     tbl
GROUP BY A,B
HAVING   COUNT(*) > 1

edited Aug 17 '12 at 22:35

answered Aug 17 '12 at 22:26

Zane Bien

just curious.. In this old post http://stackoverflow.com/questions/6870397/selecting-distinct-combinations the answer doesn't have the having clause.. but its accepted... :P and the first one "SELECT DISTINCT Latitude, Longitude FROM Coordinates" is clearly wrong... – Ank Aug 17 '12 at 22:31
1

@Ankur, after the `GROUP BY` is performed, you have access to aggregate information such as the count of rows in each group, or sum, average, etc. Based on your example data, the "grouping" of (1,2) has an aggregate count of 2, whereas the other two groups only have an aggregate count of 1. By specifying `> 1`, we essentially pick all "groups" which contain one or more duplicates for the combination of A and B. – Zane Bien Aug 17 '12 at 22:31
1

@Ankur, that's because that question only wanted DISTINCT values ***regardless*** of whether or not there were duplicates. In your case, you only want rows that ***HAVE*** duplicates, and not rows that ***DON'T HAVE*** duplicates. That's the difference. – Zane Bien Aug 17 '12 at 22:34

1 Answers1