I think this works even if there are less than 3 cities listed for a country.
If there's a tie for 3rd it'll show all of them.
create table countries (country text, city text, population int);
insert into countries values ('UK', 'London', '10'), ('UK', 'Birmingham', 9),
('UK', 'Manchester', '8'), ('UK', 'Podunk', 1), ('USA', 'New York', 10),
('USA', 'Los Angeles', 9), ('USA', 'Chicago', 8), ('USA', 'Podunk', 1),
('Canada', 'Podunk', 1);
select * from countries
where population >=
(select min(population) from
(select population from countries as c
where c.country = countries.country
order by population desc limit 3
)
)
order by country, population desc;
country city population
------------ ------------ ------------
Canada Podunk 1
UK London 10
UK Birmingham 9
UK Manchester 8
USA New York 10
USA Los Angeles 9
USA Chicago 8
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Bart Smissaert
Sent: Friday, November 17, 2017 3:58 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL top 3
Say I have a table like this:
CREATE TABLE COUNTRIES(COUNTRY TEXT, CITY TEXT, POPULATION INTEGER)
What would be the SQL to get 3 cities for each country with the highest 3
populations for
that country? So, for example for the United Kingdom it would show London,
Birmingham, Manchester in that order, and for the USA New York, Los
Angeles, Chicago. So, I would like
these top 3 cities for all countries.
RBS
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
