Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday, August 14, 2013 2:17 PM To: Peter Aronson; General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY I seem to recall having read that as well. I believe, however, that MySQL does allow it, but I think it defaults to base table when available. Also, a modified form of the test case: DROP TABLE #t1 CREATE TABLE #t1(m VARCHAR(4)); INSERT INTO #t1 VALUES('az'); INSERT INTO #t1 VALUES('by'); INSERT INTO #t1 VALUES('cx'); SELECT '1', right(m,1) AS m1 FROM #t1 ORDER BY m1; SELECT '2', right(m,1) AS m1 FROM #t1 ORDER BY m1 Works, returning: Untitled1 m1 --------- -- 1 x 1 y 1 z Untitled1 m1 --------- -- 2 x 2 y 2 z -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Aronson Sent: Wednesday, August 14, 2013 2:13 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and Darwen, and it states (in a footnote on page 151) that name specified for a scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or HAVING clause as it is a column in the derived table, not the base table. Peter From: Richard Hipp <d...@sqlite.org> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 9:59 AM >Subject: Re: [sqlite] name resolutionn in GROUP BY > > >On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn ><rob.golste...@mapscape.eu>wrote: > >> create table test(name); >> insert into test values (NULL); >> insert into test values ('abc'); >> >> select count(), >> NULLIF(name,'abc') AS name >> from test >> group by lower(name); >> > >So the question is, should the "name" symbol in the GROUP BY clause >refer to the original column name in the TEST table, or should it refer >to the result column called "name". SQLite version 3.7.15 picks the >TEST table column. Version 3.7.17 picks the result column. > >Anybody know which is correct? > >-- >D. Richard Hipp >d...@sqlite.org >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
