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
