On 10 January 2013 17:04, Eduardo Morras <emorr...@yahoo.es> wrote: > On Thu, 10 Jan 2013 14:53:52 +0100 > E.Pasma <pasm...@concepts.nl> wrote: > >> Hello, >> >> A query of the form: "SELECT max(x), y FROM table" returns the value >> of y on the same row that contains the maximum x value. > > True > >> I just want to point to a construction where one would expect this to >> work however it does not. I tried a query that returns only the value >> of y and intuitively wrote: >> >> select y from (select max(x), y from t); >> > > Select max(x), y from t will return 2 colums and n rows, the first column > with the same value, the maximum of x in table t, the second column all t.y > values. Something like this: > > max(x) | y > ----------------- > 500 | 5 > 500 | 3 > 500 | 9 > 500 | 2 > 500 | 31 > 500 | 1 > 500 | 86 > 500 | 64
Not what I see... > > From this result table, you are doing select y from (result table) and > getting only the y values as you expected > > y > ----- > 5 > 3 > 9 > 2 > 31 > 1 > 86 > 64 > >> This however no longer returns the value of y corresponding to the >> maximum x. > > For me it works, it shows all y from t. Perhaps i have misunderstood something SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t( x integer, y integer ); sqlite> sqlite> sqlite> insert into t values( 1, 2 ); sqlite> insert into t values( 4, 3 ); sqlite> insert into t values( 10, 5 ); sqlite> insert into t values( 2, 6 ); sqlite> sqlite> select max(x),y from t; 10|5 sqlite> select x,y from (select max(x) x, y from t); 10|5 sqlite> select y from (select max(x) x, y from t); 6 This does not look right... > >> >> It looks a consequence of query optimization. The query satisfies all >> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html >> . The max(x) column is then eliminated. >> >> Hope this is useful to know for who is using the feature. >> >> EPasma >> > > --- --- > Eduardo Morras <emorr...@yahoo.es> Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
