Filter duplicate records and retain 1 record

I was able to get the duplicate records and I wanted to retain 1 record per item barcode based on the createdate (oldest)

sample script of getting the duplicate records:

select pl5.refcode, pl5.barcode, pl5.name, pl5.status, pl5.createDate
from pList6 pl6
inner join plist5 pl5 on pl6.barcode = pl5.barcode
order by pl5.barcode, pl5.createDate;

output:

**refcode      barcode          ItemDescription       status        createDate**
0075345        480000234512     Shampoo A             A             2017-09-22 10:12:29
0002345        480000234512     Shampoo A             A             2019-09-22 13:07:29
0010388        790000345217     Shampoo B             A             2018-05-13 08:34:01
0023754        790000345217     Shampoo B             A             2019-10-17 09:45:11

---

final output should be like this: (retain the items that has the oldest createdate from the 2 duplicate Barcodes)

**refcode      barcode          ItemDescription       status        createDate**
0075345        480000234512     Shampoo A             A             2017-09-22 10:12:29
0010388        790000345217     Shampoo B             A             2018-05-13 08:34:01

Answer

I guess you need first record based on create date per barcode. If you are using MySQL version 8.0 or higher, You may use window function –

SELECT refcode, barcode, ItemDescription, status, createDate
FROM (SELECT refcode, barcode, ItemDescription, status, createDate, ROW_NUMBER() OVER(PARTITION BY barcode ORDER BY createDate) RN
      FROM pList6 pl6
      inner join plist5 pl5 on pl6.barcode = pl5.barcode) RANK
WHERE RN = 1;

If you are using lower versions you may try below –

SELECT pl6.refcode, pl6.barcode, pl6.ItemDescription, pl6.status, pl6.createDate
FROM pList6 pl6
LEFT JOIN pList6 pl7 ON pl6.barcode = pl7.barcode
                     AND pl6.ItemDescription = pl7.ItemDescription
                     AND pl6.status = pl7.status
                     AND pl6.createDate < pl7.createDate
INNER JOIN plist5 pl5 ON pl6.barcode = pl5.barcode