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;

sqlite-users mailing list

Reply via email to