On Fri, May 4, 2012 at 10:39 AM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, May 4, 2012 at 10:33 AM, Gabor Grothendieck <ggrothendi...@gmail.com >> wrote: > >> On Fri, May 4, 2012 at 10:20 AM, Richard Hipp <d...@sqlite.org> wrote: >> > On Fri, May 4, 2012 at 10:06 AM, Rob Richardson < >> rdrichard...@rad-con.com>wrote: >> > >> >> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11: >> >> Queries of the form: "SELECT max(x), y FROM table" returns the >> >> value of y on the same row that contains the maximum x value. >> >> >> >> Is that standard SQL behavior? I'd have expected that to return one row >> >> for every row in the table. To get the behavior described above, I'd >> use >> >> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)". >> >> >> > >> > It is definitely NOT standard behavior. The standard behavior is >> > undefined. Or (with many SQL engines) it will throw an error if you >> have a >> > term in the result set that is not part of an aggregate function or an >> > element of the GROUP BY clause. But lots of newbies expect SQL to work >> as >> > described in the 3.7.11 release comments, and we used to get support >> > questions because it did not. And so rather than continue to answer the >> > questions over and over, I figured it would be easier to tweak SQLite to >> > reliably do what newbies expect. I never anticipated that this change >> > would be so controversial or confusing. >> > >> > All the existing, portable, documented ways to find the maximum element >> of >> > one column while simultaneously finding the other elements in the same >> row, >> > continue to work as they always have. You are not required to use this >> new >> > approach. In fact, if you want your SQL to be portable, you should >> > probably avoid it. By adding this feature, we had hoped to help >> > application developers avoid a common SQL programming error. That's all. >> > There is nothing profound going on here. >> >> Can't anyone answer the question directly? I would still like to know >> precisely what works and what does not. Its not possible to rely on >> general SQL documentation for this so I think its important to >> document it exactly. Otherwise, we are left to examine the source >> code or use trial and error (and these methods only tell you how it >> works but not how its intended to work and they could be different if >> there are bugs). If the documentation does exist please point me to >> it but I did not find it on my own. >> > > If a single min() or max() aggregate function appears in a query, then any > other columns that are not contained within aggregate functions and that > are not elements of the GROUP BY will take values from one of the same rows > that satisfied the one min() or max() aggregate function.
Thanks! I expect that this will be useful for me for at least quick and dirty computations. I suggest that this statement be added to the docs if its not already there. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
