mysqlstored-proceduresselect-query

Mysql Code To Mark New And Duplicate Values


I have a mysql table (shown below) with some values, I need to get the new values and duplicate values

I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;

But it return only the duplicate count. How to find this ???

Thank You

Table :

   Id   SerialNumber   ConfigDate  ProductId
    ---+--------------+-----------+---------
    1    11111          2018-12-22    1
    2    22222          2018-12-22    2
    3    33333          2018-12-22    3
    4    11111          2018-12-23    1

Product Table :

 ProductId    Name
-----------+------
  1           a
  2           b
  3           c

Expected Result

Id   SerialNumber   ConfigDate   Type      Name
---+--------------+------------+----------+-------
1    11111          2018-12-22   New          a
2    22222          2018-12-22   New          b
3    33333          2018-12-22   New          c
4    11111          2018-12-23   Duplicate    a

Solution

  • You can try below -

    DEMO

    select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as  Type,
    productid,name
    from
    (
    SELECT 
        @row_number:=CASE
            WHEN @SerialNumber = SerialNumber THEN @row_number + 1
            ELSE 1
        END AS num,
        @SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
    FROM
        t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
    order by SerialNumber
    )A