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. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
