Olivier Mascia wrote:
>> Le 10 avr. 2018 à 13:20, Clemens Ladisch <clem...@ladisch.de> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to