Top N Values in Access 2002

[sdelacruz8]

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

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

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

 

[Remou]

How about:

SELECT Top 2 Country, City, Population
FROM Table
ORDER BY Population


If two or more places have the same population, more that two rows will be returned.

 

[ba4crm]

Create the query, add the country, city, and population. For the population, sort it by descending and then in the toolbar for top values (typically is next to the sum "sigma" sign), enter 2.

 

[r937]

he wants the top 2 per country, not the top 2 overall

SELECT Country    
     , City    
     , Population
  FROM DaTable AS t
 WHERE ( SELECT COUNT(*) 
           FROM DaTable  
          WHERE Country = t.Country
            AND Population > t.Population ) < 2

r937.com | rudy.ca

 

[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]

Thank you all for the quick responses! The codes from r937 and PHV were the ones I was looking for. Thanks again!