This is quite clear in the documentation I think, and might even be made
clear in the SQL standard (but I did not check).
An integer literal (and only an integer literal) denotes the column
number to order or group by. This is true for all Databases I know of,
but that list is obviously not total.
A string literal simply groups by the value in that string, like any
other expression that evaluates to a constant value , which can only
ever return one single result since that value will be the same for all
queried rows in the result-set.
To quote directly (via copy-paste) from the documentation you
referenced, about 3/4 of the way down (emphasis by me):
...//
Each ORDER BY expression is processed as follows:
1.
If the ORDER BY expression is a *constant integer K* then the
expression is considered an alias for the K-th column of the result
set (columns are numbered from left to right starting with 1).
2.
If the ORDER BY expression is an identifier that corresponds to the
alias of one of the output columns, then the expression is
considered an alias for that column.
3.
Otherwise, if the ORDER BY expression is *any other expression*, it
is evaluated and the returned value used to order the output rows//...
As far as clarity goes I think the devs did an amicable job here - it's
hard for me to come up with a similarly succinct statement that would
make point 1 any clearer.
Hope this answers the question, Good luck!
Ryan
On 2017/05/24 2:07 PM, Denis Burke wrote:
The SQLite documentation (http://www.sqlite.org/lang_select.html) says the
GROUP BY clause accepts [expr]. And [expr] can be composed of a literal.
What I cannot find is what SQLite does (or should do) with a literal in the
GROUP BY clause.
In the simple case of table T1 with two columns C1,C2
CREATE TABLE [T1](
[c1] text COLLATE NOCASE,
[c2] text COLLATE NOCASE);
INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');
These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;
and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;
and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;
This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns. Maybe this is obvious and
documented somewhere, but I was not able to find it and was not obvious to
me initially.
But when the literal GROUP BY is a string:
These all produce a single row of output (and it happens to be the last row
inserted [a1,b5]):
select c1,c2 from t1 group by '1';
select c1,c2 from t1 group by '2';
select c1,c2 from t1 group by '3';
select c1,c2 from t1 group by 'c1';
In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op. But I
cannot find documentation to justify my expectation, or to validate the
actual output.
I did compare to SQL Server to see if behavior was similar and SQL Server
simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."
So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?
Thanks,
Denis
_______________________________________________
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
