CODEWITHSUNDEEP
sqloracle-database
user2786962
user2786962

Reputation: 469

How to create a flag of exclusion for duplicate rows in Oracle

Given below is the snapshot of my data

NameAgeIncome Group
Asd 20  A
Asd 20  A
b   19  E
c   21  B
c   21  B
c   21  B
df  21  C
rd  24  D

I want ot include a flag variable where it says 1 to one of the duplicate row and 0 to another. And also 0 to rest of the rows which are not duplicate. Given below is the snapshot of final desired output 

NameAgeIncome Group Flag
Asd 20  A   1
Asd 20  A   0
b   19  E   0
c   21  B   1
c   21  B   1
c   21  B   0
df  21  C   0
rd  24  D   0

Can anyone help me how to create this Flag variable in Oracle database

Upvotes: 0

Views: 268

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this using analytic functions and case:

select t.*,
       (case when row_number() over (partition by name, age, income order by name) = 1
             then 0 else 1
        end) as GroupFlag
from table t;

Upvotes: 2

Related Questions