Subquery with multiple sorted results

Struggling on a most probably very simple query for MS Access, but couldn’t nail it for 3 days already.

Any help would be highly appreciated..

There are 2 tables – Regions (REGNAME) and Cities (REGNAME, NAME, POPULATION)

I need to get the top 10 cities sorted by the population in descending order for each region.

There’s no problem to do this for some certain Region:

SELECT TOP 10 Cities.REGNAME, NAME from Cities, Regions 
WHERE Cities.REGNAME = Regions.REGNAME 
AND Regions.REGNAME = 'SOME_REGION_NAME' 
ORDER BY POPULATION DESC

​x
 
SELECT TOP 10 Cities.REGNAME, NAME from Cities, Regions WHERE Cities.REGNAME = Regions.REGNAME AND Regions.REGNAME = 'SOME_REGION_NAME' ORDER BY POPULATION DESC​

But I need to see the similar list of Cities for any number of Regions. For every region in subquery I should see sorded 10 cities’ list.

Like below:

SOME_REGION_NAME - CITY 1
SOME_REGION_NAME - CITY 2
SOME_REGION_NAME - ..
SOME_REGION_NAME - CITY 10

ANOTHER_REGION_NAME - CITY 1
ANOTHER_REGION_NAME - CITY 2
ANOTHER_REGION_NAME - ..
ANOTHER_REGION_NAME - CITY 10

SOME_OTHER_REGION_NAME - CITY 1
SOME_OTHER_REGION_NAME - CITY 2
SOME_OTHER_REGION_NAME - ..
SOME_OTHER_REGION_NAME - CITY 10

 
SOME_REGION_NAME - CITY 1SOME_REGION_NAME - CITY 2SOME_REGION_NAME - ..SOME_REGION_NAME - CITY 10​ANOTHER_REGION_NAME - CITY 1ANOTHER_REGION_NAME - CITY 2ANOTHER_REGION_NAME - ..ANOTHER_REGION_NAME - CITY 10​SOME_OTHER_REGION_NAME - CITY 1SOME_OTHER_REGION_NAME - CITY 2SOME_OTHER_REGION_NAME - ..SOME_OTHER_REGION_NAME - CITY 10​

This query gives 10 cities in total from 3 regions:

SELECT TOP 10 Cities.REGNAME, NAME from Cities, Regions 
WHERE Cities.REGNAME = Regions.REGNAME 
AND Regions.REGNAME IN ('SOME_REGION_NAME' , 'ANOTHER_REGION_NAME', 'SOME_OTHER_REGION_NAME')
ORDER BY POPULATION DESC

 
SELECT TOP 10 Cities.REGNAME, NAME from Cities, Regions WHERE Cities.REGNAME = Regions.REGNAME AND Regions.REGNAME IN ('SOME_REGION_NAME' , 'ANOTHER_REGION_NAME', 'SOME_OTHER_REGION_NAME')ORDER BY POPULATION DESC​

But I expect to see a 30 cities table, not 10.

Please, someone, show me the way 🙂

Thank you

Answer

You can use a sub-query to select the top 10 cities for each region:

SELECT R.REGNAME, C.NAME, C.POPULATION
FROM Cities AS C INNER JOIN Regions AS R ON C.REGNAME = R.REGNAME
WHERE C.NAME IN (SELECT TOP 10 [NAME] FROM Cities AS C1 WHERE C1.REGNAME=R.REGNAME ORDER BY C1.POPULATION DESC)
ORDER BY R.REGNAME ASC, C.POPULATION DESC

 
SELECT R.REGNAME, C.NAME, C.POPULATIONFROM Cities AS C INNER JOIN Regions AS R ON C.REGNAME = R.REGNAMEWHERE C.NAME IN (SELECT TOP 10 [NAME] FROM Cities AS C1 WHERE C1.REGNAME=R.REGNAME ORDER BY C1.POPULATION DESC)ORDER BY R.REGNAME ASC, C.POPULATION DESC​

You may get more than 10 cities being returned for a region if there is a tie on the population.

Note that as well as it being better to use a foreign key to store the region in the cities table, NAME is a reserved word in Access, so you should rename the field – in the example SQL, I’ve just wrapped it in square brackets.

Regards,

Advertisement

Answer