At first I wasn't sure what you meant by "column identifier". It's what
the documentation calls "column-alias". But this also qualifies as an
"other expression" doesn't it? A column alias can appear pretty much
anywhere any expression can, except in the same column list where it is
defined.
Mark
On 31/05/18 18:27, Hick Gunter wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
