Seems to return 26 rows for the current tip, what version are you experiencing the issue with?
SQLite version 3.25.0 2018-09-03 17:11:11 Enter ".help" for usage hints. sqlite> .version SQLite 3.25.0 2018-09-03 17:11:11 f1138a38bd23f201a35621a71e82c5718abddb42ab82938e9516ab9d43e4alt2 zlib version 1.2.11 gcc-8.1.0 sqlite> 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 ...> ; Argentina|56|37.62 Australia|55|37.62 Austria|7|42.62 Belgium|8|37.62 Brazil|1|39.62 Canada|3|39.62 Chile|57|46.62 Czech Republic|6|49.62 Denmark|9|37.62 Finland|44|41.62 France|43|40.62 Germany|37|43.62 Hungary|45|45.62 India|58|38.62 Ireland|46|45.62 Italy|47|37.62 Netherlands|48|40.62 Norway|4|39.62 Poland|49|37.62 Portugal|34|39.62 Spain|50|37.62 Sweden|51|38.62 USA|26|47.62 United Kingdom|52|37.62 United Kingdom|53|37.62 United Kingdom|54|37.62 sqlite> --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Edson Poderoso >Sent: Monday, 3 September, 2018 07:32 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Incorrect result when using WHERE with with >correlated sub-select query > >Using the chinook database available at: >https://github.com/lerocha/chinook-database > >The following query should return 26 rows, instead I returns 15. > > >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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
