On Sun, 14 Sep 2014 00:18:34 -0400, Lea Verou <l...@verou.me> wrote: > Per the 3.7.11 changelog [1], queries of the form > SELECT max(x), y FROM table return the value of y > from the same row that contains the maximum x value. > However, this: > select y from (SELECT max(x), y FROM table); > would not return the same y rows. This would work as expected: > select m, y from (SELECT max(x) as m, y FROM table); > > [1]: http://www.sqlite.org/changes.html#version_3_7_11
Confirmed. However, SELECT max(x), y FROM t1 is not proper SQL, as a non-aggregate expression (in this case column y) is used that doesn't appear in a GROUP BY clause. SQLite allows it, and in the simplest of statements it returns a value of y from one of the rows that matches x=max(x), as promised. Apparently, in SELECT y FROM (SELECT max(x), y FROM t1); it is optimized out somehow. Workaround: SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1); which may return multiple rows, so you'd have to use LIMIT 1 or max(y) or min(y), whichever is most appropriate for your use case. I agree that the result is quite unexpected. Test script and results on 3.8.7 2014-09-06 17:06:13 ad7063aa1a0db32cdbe71815545b2edca57d3bcc and 3.8.7 2014-09-12 20:30:59 b332a84d5154f70f3197537df4af243eaebbb011: CREATE TABLE t1 (x INTEGER, y INTEGER PRIMARY KEY); INSERT INTO t1 (x,y) VALUES (1,1); INSERT INTO t1 (x,y) VALUES (2,2); INSERT INTO t1 (x,y) VALUES (3,3); INSERT INTO t1 (x,y) VALUES (3,4); INSERT INTO t1 (x,y) VALUES (2,5); SELECT * FROM t1; 1 1 2 2 3 3 3 4 2 5 SELECT max(x), y FROM t1; 3 3 SELECT y FROM (SELECT max(x), y FROM t1); 5 SELECT m, y FROM (SELECT max(x) as m, y FROM t1); 3 3 SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1); 3 4 -- Groet, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
