CODEWITHSUNDEEP
mysqlsql
Mera
Mera

Reputation: 3

Limit results of one column

I have two tables region(name, number), city(region_number, name, population), and I want to return the first 5 cities with highest population along with the region's name. This is my query, but it only returns the 5 cities of one region(North in this case) instead of all regions:

SELECT region.name AS region_name, city.name AS city_name, city.population AS population
FROM region INNER JOIN (SELECT city.name, city.population, city.region_code
FROM county  LIMIT 5) AS city
ON region.code = city.region_code;

Desired output:

     region_name |        city_name         | population 
------------+----------------------------+------------
 NORTH     | A                           |     226338
 NORTH     | B                           |      13656
 NORTH     | C                           |       9478
 NORTH     | D                           |       2464
 NORTH     | E                           |       2464
 WEST      | A                           |     226338
 WEST      | B                           |      13656
 WEST      | C                           |       9478
 WEST      | D                           |       2464
 WEST      | E                           |       2464

Thank you for your help in advance :)

Upvotes: 0

Views: 140

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

As a general rule, don't use limit without order by. You are getting five arbitrary cities.

What you are trying to do is a bit of a pain in MySQL. The following should work, though:

SELECT r.name AS region_name, c.name AS city_name, c.population AS population
FROM region r INNER JOIN 
     county c
     ON r.code = c.region_code
WHERE 5 >= (SELECT count(*)
            FROM county c2
            WHERE c2.region_code = c.region_code and c2.population >= c.population
           )
ORDER BY region, population DESC;

The where clause counts the number of cities with a population as big or bigger than the given city. The only cities returned are those in the top 5.

Upvotes: 2

user4211957
user4211957

Reputation:

LIMIT is your SQL keyword friend, use SELECT ... ORDER BY population LIMIT 5.

Upvotes: 2

Related Questions