Search code examples
mysqldistinct

Selecting distinct pairs in MySQL

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.

Solution

  • 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