Skip to content
Advertisement

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:

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:

This query gives 10 cities in total from 3 regions:

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

Please, someone, show me the way 🙂

Thank you

Advertisement

Answer

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

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,

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement