MariaDB 10.2

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

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