Flag duplicates in MySQL select query output

Question

My sql query so far has a few joins on tables and the final output looks like this:

FLAG        id      name
----        ---     ----
OK          21      ken
OK          34      mon
OK          51      jil
OK          51      jil
OK          71      jil
OK          80      ron
OK          91      ron

Now I want the FLAGs of duplicate names be shown as 'dup' of the lowest id:

FLAG        id      name
----        ---     ----
OK          21      ken
OK          34      mon
OK          51      jil
dup_51      51      jil
dup_51      71      jil
OK          80      ron
dup_80      91      ron

I can do it by using shell/perl script on the records stored in a file, but need to know if it can be possible by manipulating my SQL query.. Thanks for your time and help.

this is rather non-trivial. any reason you have to do it sql-side rather than in the client? — Marc B
– Marc B, Commented Oct 15, 2012 at 18:48
you need to have a unique row ID for every row, even dupes. then you select min(rowID) for every unique composite of id and name, and update that rowID as OK, everything else as dupe — amphibient
– amphibient, Commented Oct 15, 2012 at 18:49
if you want to do it in mysql, check user defined variables and case staments.. — Venu
– Venu, Commented Oct 15, 2012 at 18:49

Barranka · Accepted Answer · 2012-10-15 18:54:12Z

2

I would suggest a subquery that tracks duplicate entries on name and returns the minimum id for each duplicate. I'll asume your table is named tbl:

select name, min(id) as dupId
from tbl
group by name
having count(name) > 1

Now you can join this with your original table:

select 
    tbl.*, if(not isnull(dupId), 'Ok', concat('dup_', dupId)) as flag2
from 
    tbl
    left join (
        select name, min(id) as dupId
        from tbl
        group by name
        having count(name) > 1) as a on tbl.name = a.name

This is a way to do it. Hope it helps you.

answered Oct 15, 2012 at 18:54

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

2 Comments

Drek Over a year ago

Thanks for this. I, however, did this in a shell script: processed the old output file line by line and compared with the prev line.

Barranka Over a year ago

@Drek well.. that was the best solution anyway (just like Mark B commented)... however, if you need to do it inside mysql, you need to use some subquery. happy to help

Collectives™ on Stack Overflow

Flag duplicates in MySQL select query output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related