> Le 10 avr. 2018 à 18:25, Clemens Ladisch <clem...@ladisch.de> a écrit : > > Olivier Mascia wrote: >> Would you mean: >> >> select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME >> is not NULL; >> >> is more logically appropriate? > > Probably. The first query can give quite random results: > > delete from SHEETS; > insert into SHEETS(NODE, SHEET, NAME) values(1, 1, 'a'); > insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a'); > select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is > not NULL; > a|2 > select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME > is not NULL; > a|1 > delete from SHEETS; > insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a'); > insert into SHEETS(NODE, SHEET, NAME) values(2, 1, 'a'); > select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is > not NULL; > select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME > is not NULL; > a|1 > >> Or that I might even go for: >> >> select NAME,count(NAME) from SHEETS where SHEET>? and NAME is not NULL >> group by NAME; > > This always has the same result as the second query. (But "NAME is not NULL" > in the > WHERE clause applies to the table column, while in the HAVING clause, it > applies to > the result of the grouping. So the last query makes it more likely that > SQLite can > prove that it can use the index.)
Thanks a lot: this proved wonderfully educative, especially the oddities arising from: > select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is > not NULL; -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
