Duplicate Queries .... getting only the records I want to delete

[knpaddac]

I have a database of basketball game information. In the process of uploading the data (that was in Excel) a pretty significant amount of games got put in twice. I've figured out how to seek out all instances of these with a duplicate query, but is there any way I could simply modify that query so it only returns the record with the higher key value (in this case it is called "GameID')? Currently the return may include 100 records but I am only wanting it to be 50. My end goal is to then be able to delete the unwanted records all in that single query. If it helps, below is the current SQL for the duplicate query as it stands now:




SELECT GAMES_current.Season, GAMES_current.GameDate, GAMES_current.Winner, GAMES_current.Loser, GAMES_current.GameID, GAMES_current.WinScore, GAMES_current.LossScore
FROM GAMES_current
WHERE (((GAMES_current.Season) In (SELECT [Season] FROM [GAMES_current] As Tmp GROUP BY [Season],[GameDate],[Winner],[Loser] HAVING Count(*)>1 And [GameDate] = [GAMES_current].[GameDate] And [Winner] = [GAMES_current].[Winner] And [Loser] = [GAMES_current].[Loser]) And (GAMES_current.Season)=2017))
ORDER BY GAMES_current.Season, GAMES_current.GameDate, GAMES_current.Winner, GAMES_current.Loser;

[June7]

Why would it matter which GameID is saved? A query using TOP N could return the latest ID record for each game. Review
http://allenbrowne.com/subquery-01.html#TopN.
Removing duplicates will likely involve saving records to another table. Review https://www.techrepublic.com/article...-access-query/

[JSR]

Deleting duplicates is much harder than simply eliminating their existence in a new table. Use query design, group by each data field, NewGroupID: max(GroupID), make new table. Rename NewGroupID to GroupID in the new table if you want.

[knpaddac]

I suppose it actually doesn't matter which is saved, I just need to isolate one half of each instance so that I can then delete them. Not certain if I am just dense, but I could not figure out how to get the Top N function to work with what I have.

Why would it matter which GameID is saved? A query using TOP N could return the latest ID record for each game. Review
http://allenbrowne.com/subquery-01.html#TopN.
Removing duplicates will likely involve saving records to another table. Review https://www.techrepublic.com/article...-access-query/