SQL Query to get limited number of results based on column entry

Question

I'm struggling with SQL right now.

Ive got a table cities that contains the rows id, name, country, population.

ID  |     name     |  country  | population 
1   | Shanghai     | CN        | 14608512
2   | Buenos Aires | AR        | 13076300
3   | Mumbai       | IN        | 12691836
4   | Karachi      | PK        | 11624219
5   | Beijing      | CN        |  7480601
6   | Wuhan        | CN        |  4184206
7   | Berlin       | DE        |  3426354
8   | Puyang       | CN        |  3590000

The database contains worldwide data from all cities with a population higher than 15000. Now i'm trying to get 50 of the world's biggest cities by population.

SELECT * FROM cities ORDER BY population DESC LIMIT 0,50

My problem right now is that i get like 12 cities in China, 4 in India etc. But i need to limit my results by country, so only 2 cities per country are returned.

Results by example above:

ID  |     name     |  country  | population 
1   | Shanghai     | CN        | 14608512
2   | Buenos Aires | AR        | 13076300
3   | Mumbai       | IN        | 12691836
4   | Karachi      | PK        | 11624219
5   | Beijing      | CN        |  7480601
7   | Berlin       | DE        |  3426354

But like I said, i'm struggling with the right sql query for this kind of operation. I tried GROUP BY ... HAVING and various subselects, but none seemed to return the right resultset.

score 1 · Answer 1 · answered Aug 23 '12 at 12:52

try this:

select * from
    (select t.* from t_population t
    join
        (select  country,MAX(population) as population
         from    t_population
         group by country)a
    on  t.country=a.country
    and t.population=a.population
union all
    select t.* from t_population t
    join
        (select country,MAX(population) as population
         from   t_population t1
         where  population <> (select MAX(population) 
         from  t_population t2 where t1.country=t2.country)
         group  by country)b
    on t.country=b.country
    and t.population=b.population)c
order by  population desc

SQL Fiddle Demo

thanks man, this really seems to work! :) you gonna save my day tomorrow! — michi, Aug 23 '12 at 21:34

SQL Query to get limited number of results based on column entry

1 Answers1

SQL Fiddle Demo