views:

83

answers:

3

I have a table with columns id, a, and b.

a+b should be unique, but this is a legacy database that is not constrained correctly. How can I get a set of ids for records in which a+b is not unique?

If I have

ID A B
1  2 3
2  2 3
3  1 3
4  1 4

Then I want to get records 1 and 2 back from the query.

+2  A: 
select
    id, a, b

from your_table t

join (select a, b from your_table group by a, b having count(1) > 1) dup on dup.a = t.a and dup.b = t.b
Adam Robinson
Damn, you beat me to it. Even down to method (JOIN) and wording. But mine does not scroll. Hah! ;-) +1 for being faster.
Tomalak
+1  A: 

To get back rows 1 and 2 (as you stated), use this:

SELECT
  *
FROM
  your_table
  INNER JOIN (
    SELECT a, b FROM your_table GROUP BY a, b HAVING COUNT(*) > 1
  ) dupes ON 
    your_table.a = dupes.a AND
    your_table.b = dupes.b
Tomalak
Looks familiar ;)
Adam Robinson
*lol* But your's was not that way from the start. Nobody can prove it, but *I know*. ;-)
Tomalak
A: 

Slightly faster using window functions:

select *
from (
     select
        a
     ,  b
     ,  cnt = count(*) over ( partition by a, b )
     from your_table
) x
where cnt > 1; /* Dupe */
John Gibb