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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 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 <d...@sqlite.org> wrote: > 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? > 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 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