By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t1(id int, serialnumber int, configdate date,productid int)
insert into t1(id, serialnumber,configdate,productid)
values(1,1111,'2018-12-22',1),
(2,2222,'2018-12-22',2),
(3,3333,'2018-12-22',3),
(4,1111,'2018-12-23',1)
create table product(pid int, name varchar(10))
insert into product(pid,name)
values(1,'a'),(2,'b'),(3,'c')
select id, num,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
| id | num | SerialNumber | ConfigDate | Type | productid | name |
|---|---|---|---|---|---|---|
| 1 | 1 | 1111 | 2018-12-22 | New | 1 | a |
| 4 | 2 | 1111 | 2018-12-23 | Duplicate | 1 | a |
| 2 | 1 | 2222 | 2018-12-22 | New | 2 | b |
| 3 | 1 | 3333 | 2018-12-22 | New | 3 | c |