Deleting duplicate table entries leaving 1

[crossy5575]

Hi all I have a table with 4 fields; id (PK), xdate, xdesc, country(integer), which has duplicate data,
so far i have a query showing duplicate queries 01dupspdates which creates a combination of date and country which might be duplicates

DELETE SpecialDates.xDate, SpecialDates.Country, SpecialDates.id, SpecialDates.xDesc
FROM SpecialDates
WHERE (((SpecialDates.xDate) In (SELECT [xDate] FROM [SpecialDates] As Tmp GROUP BY [xDate],[Country] HAVING Count(*)>1 And [Country] = [SpecialDates].[Country])));

18 records in total are duplicated

I then had a second query showing the first unique id of each duplicate named 01dupspdatessum

SELECT SpecialDates.[xDate], SpecialDates.[Country], First(SpecialDates.[id]) AS FirstOfid, SpecialDates.[xDesc]
FROM SpecialDates
GROUP BY SpecialDates.[xDate], SpecialDates.[Country], SpecialDates.[xDesc]
HAVING (((SpecialDates.[xDate]) In (SELECT [xDate] FROM [SpecialDates] As Tmp GROUP BY [xDate],[Country] HAVING Count(*)>1 And [Country] = [SpecialDates].[Country])))
ORDER BY SpecialDates.[xDate], SpecialDates.[Country];

9 records are shown here

so i want to delete the all items in the first query except for those in the 2nd query, or with the id's shown in the second query.

Can someone adapt the delete query to show this? ie 18-9 records leaving the 9 remaining.

thanks

 

[ByteMyzer]

If the Key field-pair is xDate and Country, and if you only want to retain the records with the lowest ID value for each xDate and Country, then you can try a delete query like the following:

DELETE *
FROM SpecialDates
WHERE SpecialDates.id > (
    SELECT MIN(S2.id)
    FROM SpecialDates AS S2
    WHERE S2.xDate = SpecialDates.xDate
    AND S2.Country = SpecialDates.Country);

 

[crossy5575]

Thanks ByteMyser, great work!
that is so simple -
I wish i could understand how you knew how to do that!!! that circumvents the need for anyqueries!
brilliant v v happy thank you