Ponder the following select fragment SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table
Then you can have ORDER BY 1,2,3 -- integer output column numbers which is equivalent to ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other expression" -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Brand Sent: Mittwoch, 30. Mai 2018 16:22 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 ___________________________________________ 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
