I am trying to figure out how to delete the FIRST record in a set of duplicates. The intent is to keep the latest duplicated record, rather than the earliest.
Right now, I am using these queries:
Code:
SELECT tblAmazonOrderInput.*
FROM tblAmazonOrderInput LEFT JOIN (SELECT MIN(ID) as RowId, [Web Order], [MSKU]
FROM tblAmazonOrderInput
GROUP BY [Web Order], [MSKU]) AS KeepRows ON tblAmazonOrderInput.ID = KeepRows.RowID
WHERE KeepRows.RowID Is Null;
DELETE tblAmazonOrderInput.*
FROM tblAmazonOrderInput
WHERE [ID] IN
(SELECT ID FROM [qryAmazonOrderInput_Dedupe]);
Can someone help me how to revise the logic to select the first row of a duplicated set, only if there is a duplicate?
Unfortunately, it's not as easy as (SELECT MAX(ID)) or WHERE KeepRows.RowID Is Not Null. In those cases, it selects the first record of each unique [Web Order] and [MSKU], regardless if it is duplicated or not.
Also, I can't use DELETE * FROM tblAmazonOrderInput WHERE NOT IN (SELECT ID FROM [qryAmazonOrderInput_Dedupe]) because it would delete the first record of each unique [Web Order] and [MSKU], regardless if it is duplicated or not.
This would be an excellent scenario to use OVER PARTITION BY, but Access doesn't allow those commands.
Thanks for any help you can provide!!!