Top N query never finishes processing.

[PaulCL]

I have a table(Orders) with 5 million records that also contains 250 unique groups(Markets). I am trying to apply a Top N query to retrieve the top 3 records(FirmNames), based on TotalVolume, from each unique group. My current query never finishes running. Is there a more efficient way to retrieve these top 3 records? Thanks

SELECT Orders.Market, Orders.TotalVolume, Orders.FirmName INTO [Top 3 Luxury Market Final]
FROM Orders
WHERE (((Orders.TotalVolume) In (SELECT TOP 3 TotalVolume
FROM Orders AS Dupe
WHERE Dupe.Market = Orders.Market
ORDER BY Dupe.TotalVolume DESC, Dupe.FirmName DESC)))
ORDER BY Orders.Market, Orders.TotalVolume DESC , Orders.FirmName;

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/microsoft-access/890652-top-n-query-never-finishes-processing.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).