Mysql Code To Mark New And Duplicate Values

Question

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

Server version: 10.1.28-MariaDB – JIJOMON K.A Jan 02 '19 at 04:48 — JIJOMON K.A, Jan 02 '19 at 04:48

Fahmi · Accepted Answer · 2019-01-02T05:17:30.477

4

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

edited Jan 02 '19 at 05:17

answered Jan 02 '19 at 04:52

Fahmi

37,315
5
22
31

1

Thanks Bro. its Working, I need to join another table with this how its possible ??? – JIJOMON K.A Jan 02 '19 at 05:05
@JIJOMONK.A, could you please add that table to your quesiton – Fahmi Jan 02 '19 at 05:09
@JIJOMONK.A, check here - https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=cb25266d766681f323e7cee304b6179f – Fahmi Jan 02 '19 at 05:16
1

Thanks Bro Its Working. Thanks You for the support. – JIJOMON K.A Jan 02 '19 at 05:32

virtualone · Answer 2 · 2019-01-02T05:43:46.760

1

the following mysql query will produce the expected result

SELECT cl.*, 
       IF (cl.id = (SELECT Min(id) 
                    FROM   `config_log` 
                    WHERE  cl.serialnumber = serialnumber 
                    ORDER  BY id ASC), 'NEW', 'DUPLICATE') AS type, 
       p.name 
FROM   `config_log` AS `cl` 
       INNER JOIN products AS p 
               ON p.id = cl.productid

Tested on mysql Distrib 5.7.24, for Linux (x86_64)

edited Jan 02 '19 at 05:43

answered Jan 02 '19 at 05:23

virtualone

76
3

Thanks Bro Its Working – JIJOMON K.A Jan 02 '19 at 06:31
how to write where condition like type='NEW' or type='DUPLICATE' – JIJOMON K.A Jan 07 '19 at 04:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements – virtualone Jan 07 '19 at 05:21

score 1 · Answer 3 · answered Jan 07 '19 at 05:33

for filtering the result you can use having like this

SELECT cl.*, 
       IF (cl.id = (SELECT Min(id) 
                    FROM   `config_log` 
                    WHERE  cl.serialnumber = serialnumber 
                    ORDER  BY id ASC), 'NEW', 'DUPLICATE') AS type, 
       p.name 
FROM   `config_log` AS `cl` 
       INNER JOIN products AS p 
               ON p.id = cl.productid 
HAVING type = 'DUPLICATE'

Mysql Code To Mark New And Duplicate Values

3 Answers3