> By the way, this feature is documented for ORDER BY, but I don't see it for > GROUP BY.
It's not standard for GROUP BY e.g. SQL Server does not support it (ORDER BY col indexes are fine there too) At least sqlite does not support the abomination GROUP BY 1 DESC the way MySQL does. cheers, </wqw> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Brand Sent: Wednesday, May 30, 2018 5:22 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0" Thanks for the clarification. > You have constant integers, output column identifiers and "any other > expression" as terms for GROUP BY. Just to make sure I'm not missing something subtle: I understand the "constant integer" is what gets interpreted as a result column number. What is an "output column identifier" then? Isn't it already covered by the broader category "any other expression"? It's still a pretty astonishing language feature(!?) that an integer numeric-literal, which in every other column-like context represents its integer value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this. By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY. Mark On 30/05/18 13:28, Hick Gunter wrote: > You have constant integers, output column identifiers and "any other > expression" as terms for GROUP BY. If the expression evalutes to a constant > value, you will have only one output row. > > -----Ursprüngliche Nachricht----- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Mark Brand > Gesendet: Mittwoch, 30. Mai 2018 12:11 > An: sqlite-users@mailinglists.sqlite.org > Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0" > > Thanks. I had forgotten that GROUP BY considers a literal integer in this > context to be a column number, a feature I don't use. > > These, on the other hand, work as I would have expected: > > sqlite> select 0 group by cast (0 as int); > 0 > sqlite> select 0 group by (select 0); > 0 > > Mark > > > On 30/05/18 12:00, Hick Gunter wrote: >> Yes. If the expression is a constant integer K, then it is considered an >> alias for the K-th column of the result set. Columns are ordered from left >> to right starting with 1. >> >> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as >> "SELECT 0 GROUP BY 31" would be. >> >> -----Ursprüngliche Nachricht----- >> Von: sqlite-users >> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von >> Mark Brand >> Gesendet: Mittwoch, 30. Mai 2018 11:32 >> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0" >> >> Hi, >> >> Is there a good reason for this error: >> >> sqlite> SELECT 0 GROUP BY 0; >> Error: 1st GROUP BY term out of range - should be between 1 and 1 >> sqlite> SELECT 0 GROUP BY 1; >> 0 >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> ___________________________________________ >> Gunter Hick | Software Engineer | Scientific Games International >> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: >> 0430013 | (O) +43 1 80100 - 0 >> >> May be privileged. May be confidential. Please delete if not the addressee. >> _______________________________________________ >> 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 > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International > GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: > 0430013 | (O) +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
