As does MS SQL 2008 R2
DROP TABLE #Test
CREATE TABLE #Test ( Val int )
INSERT INTO [#Test] ([Val]) VALUES (-2), (2)
SELECT Val FROM #Test GROUP BY Val
SELECT ABS(Val) AS Val FROM #Test GROUP BY Val
Val
---
-2
2
Val
---
2
2
Your requested test case:
Untitled1 m
--------- -
1 x
1 y
1 z
Untitled1 m
--------- -
2 z
2 y
2 x
(I used the following code)
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);
-----Original Message-----
From: [email protected] [mailto:[email protected]]
On Behalf Of Dominique Devienne
Sent: Wednesday, August 14, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolutionn in GROUP BY
On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp <[email protected]> wrote:
> On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
> <[email protected]>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?
>
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ...
SQL> create table test (name varchar2(64));
Table created.
SQL> insert into test values (NULL);
1 row created.
SQL> insert into test values ('abc');
1 row created.
SQL> select count(*), max(nullif(name, 'abc')) as name from test group
SQL> by
lower(name);
COUNT(*) NAME
---------- ----------------------------------------------------------------
1
1
SQL> select count(*), nullif(lower(name), 'abc') as name from test group by
lower(name);
COUNT(*) NAME
---------- ----------------------------------------------------------------
1
1
Not authoritative of course, but Oracle seems to agree with the previous
behavior. --DD _______________________________________________
sqlite-users mailing list
[email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
