[Dbix-class] Using 'DISTINCT ON' clause in select statement

[John Siracusa]

On 8/18/06, Mark Blythe <list at markblythe.com> wrote:
> On 8/18/06, Paul Henrich <phenrich at butler.edu> wrote:
>> It seems postgres has a strange syntax/behavior for GROUP BY that becomes
>> difficult to work with when using SQL::Abstract and when working with joined
>> tables. Basically, any column you select must be used in the GROUP BY or in
>> an aggregate function.
>
> If I'm not mistaken, that is ANSI SQL syntax.  MySQL allows you to
> select other columns without using them in the GROUP BY, but that's an
> extension of the standard.

...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...

-John