Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, John Siracusa <siracusa@xxxxxxxxxxxxxx> wrote:

> ...where "extension of the standard" here means "crazy thing that
> makes no sense."
>
>     CREATE TABLE t (a INT, b INT, c INT);
>
>     INSERT INTO t VALUES (1, 2, 3);
>     INSERT INTO t VALUES (1, 2, 4);
>     INSERT INTO t VALUES (1, 2, 5);
>     INSERT INTO t VALUES (1, 2, 6);
>
> Now guess what value c will have in the row returned by this query:
>
>     SELECT a, SUM(b), c FROM t GROUP BY a;
>
> Good old MySQL...

Sure, the columns you select must have unique values per group.  That
limitation used to be stated in the MySQL docs.  It may still be, but
I can't find it now.  Within that limitation, the extension can be
handy in some circumstances, but as with any non-standard feature,
you're increasing the pain you'll feel if you migrate to another DB in
the future.