A DISTINCT problem

Question

I have a table with multiple columns and want to find only those where a combination of A and B is distinct.

There may be several such rows and I want to get every field of the row for each distinct combination of A and B (let's say there are also columns C and D).

Clarification:

I want to say something like

for each distinct A/B combination
   get C and D

Glad for the update, but people who answered aren't alerted about your change. — OMG Ponies, May 30 '11 at 06:04

OMG Ponies · Answer 1 · 2011-05-30T06:03:30.780

6

Using EXISTS:

SELECT a.c, a.d
  FROM YOUR_TABLE a
 WHERE EXISTS (SELECT NULL
                 FROM YOUR_TABLE b
                WHERE b.a = a.a
                  AND b.b = a.b
             GROUP BY b.a, b.b
               HAVING COUNT(*) > 1)

Using a JOIN:

SELECT a.c, a.d
  FROM YOUR_TABLE a
  JOIN (SELECT b.a, b.b
          FROM YOUR_TABLE b
      GROUP BY b.a, b.b
        HAVING COUNT(*) > 1) x ON x.a = a.a
                              AND x.b = a.b

EXISTS is likely preferable, if you're not going to return columns from the derived table in the SELECT clause.

edited May 30 '11 at 06:03

answered May 30 '11 at 03:58

OMG Ponies

325,700
82
523
502

Shouldn't it be `HAVING COUNT(*) =1` ? I might misunderstand the question, but it sounds to me that only rows with distinct values of (`a,b`) needed. – a1ex07 May 30 '11 at 04:13
@a1ex07: I thought the OP wanted the distinct pairs with duplicates - isn't that what "several rows of each distinct combination" infers? – OMG Ponies May 30 '11 at 04:15

score 2 · Accepted Answer · edited May 23 '17 at 12:26

2

This sounds like it should be solved using a GROUP BY but could also possibly be a UNION as specified by this question:

Select distinct from multiple fields using sql

edited May 23 '17 at 12:26

Community

1
1

answered May 30 '11 at 03:56

lomaxx

113,627
57
144
179

score 1 · Answer 3 · answered May 30 '11 at 03:58

You are not answering the question of which row to show when you have two rows with the same values for A and B. For example:

A   B   C   D
=============
0   1   5   5
0   1   4   7
0   0   1   1

If you want only unique instances of A and B, should C=5, D=5 or C=4, D=7 be returned? If you can decide how to return the values using an aggregate function, then you can use the GROUP BY A, B clause and aggregate methods on the C and D columns.

For example, the following will return the largest values of C and D:

SELECT    A, B, MAX(C), MAX(D)
FROM      myTable
GROUP BY  A, B

For a complete list of aggregate functions, check this website: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

A DISTINCT problem

3 Answers3