The number 1 references the first column of the result set, 2 the second, and 3 is an error because there are only 2 columns.
If the expression is a constant, then there is only 1 group. You are missing any meaningful information because you do not have an aggregate expression in your select list. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Denis Burke Gesendet: Mittwoch, 24. Mai 2017 14:07 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Group by Literals 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
