finding duplicate row and flagging first row as 1 and remaining duplicate rows 0

Question

I have below data in my a table,

COL1  COL2

A      X  
A      Y  
A      Z  
B      W  
B      W  
C      L  
C      L  

I want to fetch the above data with a additional flag such as, flag should be 1 for unique column value and first occurrence of duplicate should be 1 and remaining duplicate rows as 0, Expected output :

COL1  COL2   FLAG  
A      X     1  
A      Y     1  
A      Z     1  
B      W     1   -- First occurance  
B      W     0   -- Second occurance  
C      L     1   -- First occurance  
C      L     0   -- Second occurance 

I know that row_number() over(partition by COL1,COL2) will return count like below,

COL1   COL2   FLAG  
A       X     1  
A       Y     1     
A       Z     1    
B       W     2    
B       W     2   
C       L     3  
C       L     3   

but This is not I want. The second occurrence of same column values should be falgged as 0

Thanks in advance!

Answer 1

You can try this query.

using CASE WHEN expression check rn is or isn't greater than 1

SELECT t.COL1,t.COL2,CASE WHEN rn = 1 THEN 1 ELSE 0 END "FLAG"
FROM (
 SELECT *,row_number() over(partition by COL1,COL2 ORDER BY COL1) rn
 FROM T
) as t

Result

Sqlfiddle