How to mark duplicate values in another column MySQL

user_9897379 0
Viewed 36

I reviewed this forum but could not find correct answer. I am looking for best way to find duplicate values in one column and to mark them in another column.

So if there are two the same order IDs (they contain different suffixes: E - for electricity and G - for Gas) then I need flag 'Dual Fuel'. However, if the order ID appears only once then I need flag 'Single'.

I have these data example:

enter image description here

I tried to remove suffixes E and G from the Order ID to get proper duplicate values, then to use this method:

SELECT *
FROM
  (SELECT Order_ID,
          left(Order_ID, length(Order_ID)-1) AS EX
   FROM my_table
   ORDER BY left(Order_ID, length(Order_ID)-1) DESC) d1
LEFT JOIN
  (SELECT Order_ID,
          left(Order_ID, length(Order_ID)-1) AS EXS,
          COUNT(left(Order_ID, length(Order_ID)-1)) AS external_count
   FROM my_table
   GROUP BY left(Order_ID, length(Order_ID)-1)
   HAVING COUNT(left(Order_ID, length(Order_ID)-1)) > 1) d2 ON d2.Order_ID= d1.Order_ID

Thanks to that I would get Order ID and the count of occurances in next column. IF > 1 then Dual Fuel, IF less then Single.

But this seems not to work and it is messy, is there any simpliest way to acheive something like this?

Thanks, Pawel

mysql
1 Answers

You can use EXISTS in a CASE expression:

select orderid,
  case 
    when exists (
      select 1 from tablename 
      where left(orderid, length(orderid) - 1) = left(t.orderid, length(t.orderid) - 1))
      and right(orderid, 1) <> right(t.orderid, 1) 
    )  then 'Dual Fuel'
    else 'Single' 
  end result
from tablename t

If the ids have a fixed length of 9 chars then the code can be simplified:

select orderid,
  case 
    when exists (
      select 1 from tablename 
      where left(orderid, 8) = left(t.orderid, 8)
      and right(orderid, 1) <> right(t.orderid, 1) 
    )  then 'Dual Fuel'
    else 'Single' 
  end result
from tablename t
user_104988280
Related