SQL top 3

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL top 3

Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL top 3

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL top 3

Bart Smissaert
Ah, yes, that works indeed nicely.
Thanks for that.

RBS

On Fri, Nov 17, 2017 at 9:21 PM, David Raymond <[hidden email]>
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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL top 3

Klaus Maas
In reply to this post by David Raymond
Dear David,

could you help me to understand why your query does what it does?

The magic seems to be in this bit where the field country is
self-referenced:

>         where c.country = countries.country
Only: I don't understand why this does what we want it to do.

I tried to build a query of my own mimicking what your query is doing
with methods I am more familiar with:
(using a cross-join (lines 2-4), filtering out the superfluous results
(line 14), specifying the output fields explicitly (line 1) to exclude
field country added by the 2nd cross-join partner)

SELECT a.country, a.city, a.population
FROM countries AS a,
      (SELECT DISTINCT country
       FROM countries) AS d
WHERE population >=
       (SELECT min(population)
        FROM (SELECT population
              FROM countries AS c
              WHERE c.country = d.country
              ORDER BY population DESC
              LIMIT 3
             )
       )
       AND a.country = d.country
ORDER BY a.country, a.population DESC;

To me EXPLAIN QUERY PLAN looks rather similar for both.
My query plan has 3 additional lines because of the additional SELECT
(inserted between line 1 and 2 compared to your query plan).
The other lines are identical (except for the adjusted subquery count,
of course).
Admittedly, I have VERY little experience interpreting the output of
EXPLAIN QUERY PLAN.

Klaus

email signature Klaus Maas
------------------------------------------------------------------------
On 2017-11-17 22:21, David Raymond 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:[hidden email]] 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users