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
You can try below -
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