On 9/3/18, Edson Poderoso <[email protected]> wrote: > Using the chinook database available at: > https://github.com/lerocha/chinook-database > > The following query should return 26 rows, instead I returns 15.
How do you know that the correct answer is 26 rows instead of 15? I tried to download the SQLite and PostgreSQL versions of the Chinook database so that I could compare the answer and I am having difficulty just instantiating two databases that have the same number of rows for each table. So I have, so far, been unable to even determine if SQLite gets the same answer as PostgreSQL. How are you determining that the SQLite answer is wrong? > > > WITH GbC AS ( SELECT c.Country, c.CustomerId, SUM(i.total) spent > FROM Customer c > JOIN Invoice i > ON i.CustomerId = c.CustomerId > GROUP BY 1, 2), > MbC AS ( SELECT Country, MAX(spent) spent > FROM GbC GROUP BY 1) > SELECT outerr.* > FROM GbC outerr > WHERE outerr.spent = > (SELECT spent > FROM MbC WHERE Country = outerr.Country) > ORDER BY 1, 3 DESC > > > -- > > Edson Poderoso > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
