Ah, yes, that works indeed nicely. Thanks for that. RBS
On Fri, Nov 17, 2017 at 9:21 PM, David Raymond <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
