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. > > RobR > _______________________________________________ > 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
