Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY or HAVING clauses. So I'm not sure what all tests with ORDER BY are demonstrating, since the original question was about GROUP BY, which is a different thing, since ORDER BY operates strictly on the derived table. Peter
From: Marc L. Allen <mlal...@outsitenetworks.com> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 11:28 AM >Subject: Re: [sqlite] name resolution in GROUP BY > > >I understand. My previous email had the values of your original request. This >email was in response to Peter who found a reference that you could not use >derived names in a ORDER BY clause. > >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] >On Behalf Of Richard Hipp >Sent: Wednesday, August 14, 2013 2:26 PM >To: General Discussion of SQLite Database >Subject: Re: [sqlite] name resolution in GROUP BY > >On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen ><mlal...@outsitenetworks.com>wrote: > >> Heh... I forgot.. both selects below are identical, as 'lower(m1)' is >> incorrect. MS SQL does not permit further operations on the derived value. >> > >I think you also missed the name ambiguity issue. The queries are these: > >SELECT '1', substr(m,2) AS m > FROM t1 >ORDER BY m; > >SELECT '2', substr(m,2) AS m > FROM t1 >ORDER BY lower(m); > >Notice that the "m" in the ORDER BY clause might refer to column t1.m or it >might refer to the result set column labeled "AS m". The question is which >one. PostgreSQL answers t1.m for the first case and "AS m" for the second. >SQLite used to do that, but now it answers "t1.m" in both cases, which seems >to be a better fit to the SQL standard that Peter reports. > >Marc, if you can also try the query below on SQL Server, that would be most >helpful: > >SELECT '3', substr(m,2) AS m > FROM t1 >ORDER BY m COLLATE Latin1_General_CS_AS; > > >-- >D. Richard Hipp >d...@sqlite.org >_______________________________________________ >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 > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
