How to get Top N Based on 2 groups in access 2002 1

[sdelacruz8]

How do I get the top N based on 2 groups in Access 2002? For example, I have the following fields: Country, City, and Population.

I want to be able to see the Country and City with the top 2 highest population.

Country CITY Population
USA NEW YORK CITY 500
USA VIRGINIA BEACH 200
USA CHARLOTTE 400
USA LONG BEACH 600
CHINA HONGKONG 800
CHINA BEIJING 700
CHINA SHANGHAI 600

So, in this example, I want to see for USA: New York City and Longbeach, for CHINA: Hongkong and Beijing.

Thanks!

 

[PHV]

One way:
SELECT A.Country,A.CITY,A.Population
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Country=B.Country AND A.Population<=B.Population
GROUP BY A.Country,A.CITY,A.Population
HAVING Count(*)<=2

Another way:
SELECT Country,CITY,Population
FROM yourTable AS A
WHERE Population In (SELECT TOP 2 Population FROM yourTable WHERE Country=A.Country ORDER BY 1 DESC)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sdelacruz8]

Thanks PHV! The code worked perfectly!

 

[dhookom]

PH deserves a star for another great answer.

Duane
Hook'D on Access
MS Access MVP