Ok... looks like MSSQL 2008 R2 picks a different value of 'm' for cases 1 and
2. I'm not sure why 3 isn't the same as 1, though.
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 m
FROM #t1
ORDER BY m;
SELECT '2', right(m,1) AS m
FROM #t1
ORDER BY LOWER(m)
SELECT '3', right(m,1) AS m
FROM #t1
ORDER BY m COLLATE Latin1_General_CS_AS;
Untitled1 m
--------- -
1 x
1 y
1 z
Untitled1 m
--------- -
2 z
2 y
2 x
Untitled1 m
--------- -
3 z
3 y
3 x
-----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
