--- [EMAIL PROTECTED] wrote: > "Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > > I noticed something like a bug in the SQLite parser: queries with > > "group by" expression should accept only fields listed in the "group > > by" clause or aggregated fields (with sum(), max() etc). For example, > > given the table > > > > create table qqq (a text, b integer); > > > > the following query should not be accepted: > > > > select a from qqq group by b; > > > > but it is. > > SQLite accepts the above and does the right thing with it. > It is the equivalent of saying: > > SELECT a FROM (SELECT a,b FROM qqq GROUP BY b);
Not sure what you mean by the "right thing". It's not obvious why the rows returned by this GROUP BY are significant. The SQLite query above is equivalent to this query: -- works in both SQLite and Oracle select qqq.a from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; which essentially returns the entry "a" for the rows corresponding to each unique "b" with the highest rowid. The "a" values returned are basically governed by initial insert order. CREATE TABLE qqq(a,b); INSERT INTO "qqq" VALUES(1, 10); INSERT INTO "qqq" VALUES(2, 10); INSERT INTO "qqq" VALUES(3, 10); INSERT INTO "qqq" VALUES(4, 11); INSERT INTO "qqq" VALUES(5, 11); INSERT INTO "qqq" VALUES(6, 10); INSERT INTO "qqq" VALUES(-7, 10); INSERT INTO "qqq" VALUES(3, 10); INSERT INTO "qqq" VALUES(-3, 11); INSERT INTO "qqq" VALUES(4, 9); INSERT INTO "qqq" VALUES(2, 9); sqlite> select * from qqq group by b; a|b 2|9 3|10 -3|11 sqlite> select qqq.* from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; a|b 2|9 3|10 -3|11 The same data, populated in different order: sqlite> drop table qqq; sqlite> CREATE TABLE qqq(a,b); sqlite> INSERT INTO "qqq" VALUES(2, 9); sqlite> INSERT INTO "qqq" VALUES(1, 10); sqlite> INSERT INTO "qqq" VALUES(3, 10); sqlite> INSERT INTO "qqq" VALUES(2, 10); sqlite> INSERT INTO "qqq" VALUES(-3, 11); sqlite> INSERT INTO "qqq" VALUES(3, 10); sqlite> INSERT INTO "qqq" VALUES(4, 9); sqlite> INSERT INTO "qqq" VALUES(4, 11); sqlite> INSERT INTO "qqq" VALUES(5, 11); sqlite> INSERT INTO "qqq" VALUES(6, 10); sqlite> INSERT INTO "qqq" VALUES(-7, 10); sqlite> select * from qqq group by b; 4|9 -7|10 5|11 sqlite> select qqq.* from qqq, (select distinct b from qqq) d where qqq.rowid = (select max(rowid) from qqq where qqq.b = d.b) order by qqq.b; 4|9 -7|10 5|11 Does anyone have a real world use for this GROUP BY extension? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
