Find unique subset

I’m trying to query 4 columns A, B, C and D to find records where there is more than 1 D per A/B.

Relationship is hierarchical from A to D; there can be multiple Ds per C, multiple Cs per B etc.

e.g. here I want to find record 2 from column A because columns A and B are the same but C and D is different

A   B   C   D
-------------
1   1   1   1
1   1   2   1
1   1   3   1
2   1   1   1
2   1   2   2

I figured to group on all 4 & use a having count d > 1 but this oddly returns no results when there should be many.

Is there any way to do this without using a whole bunch of sub-queries, or is that the only way?

NB: data is not numerical the above is just an example. Actual data is string type.

Answer

You can group by a, b and set the condition in the having clause:

select a, b
from tablename
group by a, b
having count(distinct d) > 1

If you want all the rows and columns of the table that meet your conditions:

select t.*
from tablename t 
inner join (
    select a, b
    from tablename
    group by a, b
    having count(distinct d) > 1
) d on d.a = t.a and d.b = t.b

Advertisement

Answer