Olivier Mascia wrote: >> Le 10 avr. 2018 à 13:20, Clemens Ladisch <[email protected]> a écrit : >>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME >>> is not NULL; >> >> The SHEET value is from some random row in the group, and the NAME value is >> the same for all rows in the group. Are you sure you do not want to use >> WHERE >> instead of HAVING? > > 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.) Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
