Flag the data based on duplicate?

laknar · #1 (**permalink**) 08-12-11, 02:31

i have duplicate data in table. where i need to flag

if Col1 is duplicate and Col2 is 20 or 30 then Flag 'N'

else if Col1 is duplicate and Col2 is 20 or 30 or 40 then Flag 'Y'

Col1,Col2
10,20
10,30
20,20
20,30
20,40

Expected Output

Col1,Col2,Col3

10,20,N
10,30,N
20,20,Y
20,30,Y
20,40,Y

przytula_guy · #2 (**permalink**) 08-12-11, 02:37

have you already tried some sql or waiting for the others to find a solution for you ??
have a look at group by - having - count...
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

tonkuma · #3 (**permalink**) 08-12-11, 02:52

Quote:

if Col1 is duplicate and Col2 is 20 or 30 then Flag 'N'

else if Col1 is duplicate and Col2 is 20 or 30 or 40 then Flag 'Y'

Your requirements also duplicated.

And you didn't wrote what to do
if NOT(Col1 is duplicate and Col2 is 20 or 30 or 40)
in other words...
if (Col1 is not duplicate) or (Col2 is other than 20 or 30 or 40) then do what?

laknar · #4 (**permalink**) 08-12-11, 03:58

Col1,Col2
10,20
10,30
20,20
20,30
20,40
01,20
02,30
03,40
03,50

Expected Output

Col1,Col2,Col3

10,20,N
10,30,N
20,20,Y
20,30,Y
20,40,Y
01,20,N
02,30,N
03,40,Y
03,50,Y

Code:

if Col1 is duplicate and Col2 is 20 or 30 then Flag 'N' 
else if Not(Col1 is duplicate) and Col2 is 20  then Flag 'N'
else if Not(Col1 is duplicate) and Col2 is 30  then Flag 'N'
else Col1 is duplicate and Not(Col2 is 20 or 30) then Flag 'Y'

przytula_guy · #5 (**permalink**) 08-12-11, 04:20

have you already tried some sql or waiting for the others to find a solution for you ??
I myself I don't like "spoonfeeding" requests...

tonkuma · #6 (**permalink**) 08-12-11, 07:04

Quote:

Code:

if Col1 is duplicate and Col2 is 20 or 30 then Flag 'N' 
else if Not(Col1 is duplicate) and Col2 is 20  then Flag 'N'
else if Not(Col1 is duplicate) and Col2 is 30  then Flag 'N'
else Col1 is duplicate and Not(Col2 is 20 or 30) then Flag 'Y'

How to flag following cases?
'Y' or 'N' or other value or null?

col1, col2, flag
111, 25, ?
121, 20, ?
121, 27, ?
122, 30, ?
122, 34, ?
141, 20, ?
141, 21, ?
141, 22, ?
141, 30, ?

col1 is not duplicate and col2 is not 20 nor 30.
col1 is duplicate(2 rows only) and col2 of one row is 20 and col2 of another row is not 20 nor 30.
col1 is duplicate(2 rows only) and col2 of one row is 30 and col2 of another row is not 20 nor 30.

laknar · #7 (**permalink**) 08-12-11, 09:47

col1, col2, flag
111, 25, Y
121, 20, Y
121, 27, Y
122, 30, Y
122, 34, Y
141, 20, Y
141, 21, Y
141, 22, Y
141, 30, Y

The requirement here is we have to check only for 20 or 30 and col1 is either duplicate or unique then we have to flag as 'N'.

142,20,N
143,30,N
144,20,N
144,30,N
145,20,Y
145,30,Y
145,40,Y
146,null,Y
null,null,Y

tonkuma · #8 (**permalink**) 08-12-11, 11:13

Quote:

The requirement here is we have to check only for 20 or 30 and col1 is either duplicate or unique then we have to flag as 'N'.

I thought this was not enough, because the following case was unclear.
(Note: it is not easy to know rows with a condition are not exists.)
if col2 of a row is 20 or 30 and col1 is duplicate and there is another row with same col1 and col2 is not 20 nor 30.

My understandings of your requirements from examples are...

Code:

Conditions of flag 'N'
1) col1 is not duplicate and col2 is 20 or 30
2) col1 is duplicate two rows and one of col2 is 20 and another col2 is 30
in other words:
 all col2 with same col1 are 20 or 30. 

All other rows flag 'Y'

laknar · #9 (**permalink**) 08-12-11, 11:19

yes, exactly.

Quote:

Conditions of flag 'N'
1) col1 is not duplicate and col2 is 20 or 30
2) col1 is duplicate two rows and one of col2 is 20 and another col2 is 30
in other words:
all col2 with same col1 are 20 or 30.

All other rows flag 'Y'

i have tried and still working on several queries with Analytical functions like (Row_Number()) and Aggregate functions.

any help is greatly appreciated.

thanks.

tonkuma · #10 (**permalink**) 08-12-11, 11:35

One way may be...

Code:

Conditions of flag 'N'
1) col1 is not duplicate and col2 is 20 or 30
    COUNT(*) OVER(PARTITION BY col1) = 1
AND col2 IN (20 , 30)

2) col1 is duplicate two rows and one of col2 is 20 and another col2 is 30
    COUNT(*) OVER(PARTITION BY col1) = 2
AND MIN(col2) OVER(...) ...
AND MAX(col2) ... ...

laknar · #11 (**permalink**) 08-12-11, 12:36

Thank you tonkuma for you hint.

Code:

create table testt(col1 number,col2 varchar(5));

insert into testt values(10,'20');
insert into testt values(10,'30');
insert into testt values(20,'20');
insert into testt values(30,'30');
insert into testt values(40,'20');
insert into testt values(40,'30');
insert into testt values(40,'40');
insert into testt values(50,'40');


select col1,col2,case when (count(*) Over(partition by col1))=1 and (col2='20' or col2='30') Then 'N' 
when (count(*) Over(partition by col1))=2 and ((Min(col2) Over(partition by col1))='20' or (Max(col2) Over(partition by col1))='30') Then 'N'
else 'Y' end as FLAG  from testt

I have developed this query. is this correct?

tonkuma · #12 (**permalink**) 08-12-11, 12:57

I think
... ((Min(col2) Over(partition by col1))='20' AND (Max(col2) Over(partition by col1))='30') ...

For example:
121, '20', Y
121, '27', Y

tonkuma · #13 (**permalink**) 08-12-11, 19:29

Example of some other variations:

Code:

     , CASE
       WHEN COUNT(*)  OVER(PARTITION BY col1) <= 2
        AND MIN(col2) OVER(PARTITION BY col1) IN ('20' , '30')
        AND MAX(col2) OVER(PARTITION BY col1) IN ('20' , '30')
       THEN 'N'
       ELSE 'Y'
       END  AS flag1

     , CASE
       WHEN VARCHAR( COUNT(*) OVER(PARTITION BY col1) )
         || MIN(col2) OVER(PARTITION BY col1)
         || MAX(col2) OVER(PARTITION BY col1)
            IN ('12020' , '13030' , '22030')
       THEN 'N'
       ELSE 'Y'
       END  AS flag2

     , CASE
       WHEN SUM( CASE
                 WHEN col2 IN ('20' , '30')
                 THEN 0
                 ELSE 1
                 END
               ) OVER(PARTITION BY col1) = 0
       THEN 'N'
       ELSE 'Y'
       END  AS flag3

     , CASE
       WHEN '20:30:'
            LIKE '%' || VARCHAR( LISTAGG( col2 || ':%' )
                                 OVER( PARTITION BY col1 ORDER BY col2
                                       ROWS BETWEEN UNBOUNDED PRECEDING
                                                AND UNBOUNDED FOLLOWING )
                        , 3999 )
       THEN 'N'
       ELSE 'Y'
       END  AS flag4

tonkuma · #14 (**permalink**) 08-12-11, 23:14

More examples:

Code:

     , SUBSTR(
          'NY'
        , SIGN( SUM( CASE
                     WHEN col2 IN ('20' , '30')
                     THEN 0
                     ELSE 1
                     END
                   ) OVER(PARTITION BY col1)
              ) + 1
        , 1
       )  AS flag5

     , CHR( 78 + SIGN( SUM( COALESCE( 1 - LOCATE('20' , col2)
                                        - LOCATE('30' , col2)
                                    , 1
                                    )
                          ) OVER(PARTITION BY col1)
                     ) * 11
          ) AS flag6

     , CHR( 78 + SIGN(  COUNT(*) OVER(PARTITION BY col1)
                      - COUNT( NULLIF(  LOCATE('20' , col2)
                                      + LOCATE('30' , col2)
                                      , 0
                                     )
                             ) OVER(PARTITION BY col1)
                     ) * 11
          ) AS flag7

Internet Services

Web Development

Digital Marketing

Consumer Tech