SQL how to highlight duplicates under some conditions

Question

I need to mark dupliactes in the data buy only under some complex conditions. Let's say I have a table like this:

col1    col2
1       a
1       a
1       a
2       #B
2       #B
1       a
3       #B
3       #B
2       #B
1       a
4       #A
4       #A
5       c

I need to mark those records where:

value in col2 begins with a '#' AND ( it is a duplicate value in col2 AND it is under different values in col1).

so I need to get this:

col1    col2    newcol
1       a   
1       a   
1       a   
2       #B      1
2       #B      1
1       a   
3       #B      1
3       #B      1
2       #B      1
1       a   
4       #A  
4       #A  
5       c

the reason why rows with "#B" in col2 are marked is because it is a duplicate in col2 AND "#B" can be found under "3" and "2" (so 2 or more different values) in col1. The reson why records with "#A" are NOT marked is because while the are a duplicate in col2 they are only under one value ("4") in col1.

I am working in dashDB

Gordon Linoff · Accepted Answer · 2017-03-08T12:30:28.793

I think DashDB supports window functions. If so, you can do:

select col1, col2,
       (case when min_col1 <> max_col1 then 1 end) as flag
from (select t.*,
             min(col1) over (partition by col2) as min_col1,
             max(col1) over (partition by col2) as max_col1
      from t
     ) t;

You can also do something similar without window functions.

Here is an alternative method:

select t.*, t2.flag
from t join
     (select col2,
             (case when min(col1) <> max(col1) then 1 end) as flag
      from t
      group by col2
     ) t2
     on t.col2 = t2.col2;

SQL how to highlight duplicates under some conditions

1 Answers1