On Fri, May 4, 2012 at 9:20 AM, Richard Hipp <d...@sqlite.org> wrote: >> 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.
This is very clever. I'm not sure that an informative error message wouldn't have been better, but I think you made the right choice given SQLite3's previous behavior. A pragma by which to cause SQLite3 to return an error instead might be useful, but then, it's SQL_Lite_. Is there any way to define aggregate functions that pick a row for providing column values in non-aggregate expressions? E.g., you could have a median() or mode(), no? I don't think this is important, am just curious. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
