On Thu, 10 Jan 2013 14:53:52 +0100 E.Pasma <pasm...@concepts.nl> wrote:
> Hello, > > this mail is about the aggregate feature that was unveiled in the > release log of version 3.17.11, http://www.sqlite.org/releaselog/3_7_11.html > : > > 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 >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 > > 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
