How to create a flag for duplicate values in oracle

Question

i would like to know how to create a flag for duplicate rows in a table.for example : if i get duplicate rows it will show me 1 and if i dont get any duplicate rows,it will display 0

i tried the following for this test table i have used to check whether or not it will work

SQL> select * from t_flag 2 / EMP_NAME EMP_NO DEPT -------------------------------------------------- --------- -------------------- ROBI 1 AGRO ROBI 1 AGRO BORIS 2 IT SID 3 VET BORIS 2 IT select t_flag.*, (case when row_number() over (partition by emp_name,emp_no,dept order by emp_name) = 1 then 0 else 1 end) as GroupFlag from t_flag SQL> / EMP_NAME EMP_NO DEPT GROUPFLAG -------------------------------------------------- --------- -------------------- --------- BORIS 2 IT 0 BORIS 2 IT 1 ROBI 1 AGRO 0 ROBI 1 AGRO 1 SID 3 VET 0

what i would like to get from the result is : 1 for duplicate rows and 0 for non-duplicate rows

thanks!

@jarlh,i get duplicate rows often times in my oracle tables at work.thats why i have to check the tables before proceeding for updates,insertions etc — Iftekhar
– Iftekhar, Commented Jun 2, 2015 at 18:50

Gordon Linoff · Accepted Answer · 2015-06-02 11:43:07Z

1

You can use analytic functions with case. But the one you want is count(*) rather than row_number():

select t.*, (case when count(*) over (partition by emp_name, emp_no, dept) > 1 then 1 else 0 end) as groupflag from t_flag t;

answered Jun 2, 2015 at 11:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Iftekhar Over a year ago

thanks for your reply with the new query.it would be best if i could do the checking with all the tables using this query.

Gordon Linoff Over a year ago

The question you asked is about a single table. If you have a question about multiple tables, then please ask another question. I find it impolite when OPs change their questions and invalidate answers, because the answers then might draw downvotes.

Mykyta Halchenko · Accepted Answer · 2022-08-11 05:20:58Z

you can also do it in this way. hope it helps:)

select case when count >1 then 'D' else 'U' end as GroupFlag, emp_name, emp_no, dept from ( select emp_name, emp_no, dept, count(*) as count from t_flag group by emp_name, emp_no, dept );

Collectives™ on Stack Overflow

How to create a flag for duplicate values in oracle

2 Answers 2

2 Comments

you can also do it in this way. hope it helps:)

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

you can also do it in this way. hope it helps:)

Comments

Related