newtooca newtooca - 1 month ago 5
SQL Question

Finding unique values with multiple columns using certain condition

ID? A B C
--- -- -- --
1 J 1 B
2 J 1 S
3 M 1 B
4 M 1 S
5 M 2 B
6 M 2 S
7 T 1 B
8 T 2 S
9 C 1 B
10 C 1 S
11 C 2 B
12 N 1 S
13 N 2 S
14 N 3 S
15 Q 1 S
16 Q 1 S
17 Z 1 B


I need to find unique values with multiple column with some added condition. The unique value are combination of Col A,B and C.

If Col A has only two rows (like record 1 and 2) and the Column B is same on both data and there is a different value as in Column C then i dont need those records.

If Col A has only multiple rows (like record 3 to 6 ) with different Col B and C combination we want to see those values.

If Col A has multiple rows (like record 7 to 8 ) with different Col B and C combination we want to see those values.

If Col A has only multiple rows (like record 9 to 11 ) with similar/different Col B and C combination we want to see those values.

If Col A has only multiple rows (like record 12onwards ) with similar Col C and similar or different Column B we dont need those values...

If single value like Row 17 there is no need to display either

Tried a lot but not getting exact answer any help is greatly appreciated..

Answer

Trying to go through all the logic, I think you want all rows where the values of both columns A and B differ. An easy way to see whether records differ is by looking at the min and max values. And, you can do this using analytic functions:

select A, B, C
from (select t.*,
             count(*) over (partition by A) as Acnt,
             min(B) over (partition by A) as Bmin,
             max(B) over (partition by A) as Bmax,
             min(C) over (partition by A) as Cmin,
             max(C) over (partition by A) as Cmax
      from t
     ) t
where (Bmin <> Bmax or Cmin <> Cmax)

Your example data does not have any actual duplicates, so I don't think a count(distinct) is necessary. Your rules say nothing about what to do when A only appears once. This version will filter those rows out.