On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp wrote: > On Mon, Oct 18, 2010 at 8:19 AM, <ivoryjoh...@gmail.com> wrote: > > > I made an error in my SQL when I did not include one of my non-aggregate > > columns in my group. I was surprised that Sqlite did not catch this, and > > even more surprised when the docs spelled out this behavior. > > > > Is everyone ok with this? > > Do any other SQL engines allow this? > > (DB2 does not) > > > > I was going to change this at one point, so that it raised an error, but > that suggestion raised such an outcry that I decided to leave it. > Apparently, there are many applications out there that depend on this > behavior. > > I think the big use case is as a substitute for DISTINCT.
Perhaps there should be aggregate functions whose purpose is pick the first/last value of some expression/column as ordered by some expression (including by random). Something like: SELECT average(a), agg_random(b) FROM some_table GROUP BY c; SELECT average(a), agg_first(b ORDER BY b ASC) FROM some_table GROUP BY c; SELECT average(a), agg_last(b ORDER BY rowid ASC) FROM some_table GROUP BY c; Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
