On 2/10/2011 2:17 PM, Dan Kubb wrote: > Database setup: > > CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER > NOT NULL); > > INSERT INTO "test" ("letter", "number") VALUES('b', 1); > INSERT INTO "test" ("letter", "number") VALUES('a', 2); > INSERT INTO "test" ("letter", "number") VALUES('c', 2); > > Initial query: > > SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT > 1; > > This returns "a|2", the second row from the results as you would > expect given that we're sorting on the letter then the number. > However, here's what I did not expect: > > Initial query as a subquery: > > SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" > ORDER BY "letter", "number" LIMIT 1) AS "test"; > > This returns "1"
Yes, looks like a bug. If you drop DISTINCT, it returns a single row with the value 2. My guess is, DISTINCT is internally implemented as ORDER BY, and that overrules ORDER BY found in the subquery. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users