Is this new syntax likely to perform any better than the traditional way of writing the query?
-- E On May 4, 2012, at 11:42 AM, Nico Williams <n...@cryptonector.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
