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

Answer 1

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.