Re: [SQL] GROUP BY: v6.1 vs. v6.5.2

Ray Plante <rplante(at)ncsa(dot)uiuc(dot)edu> writes:
> "When GROUP BY is present, it is not valid to refer to ungrouped
> columns except within aggregate functions, since there would be
> more than one possible value to return for an ungrouped column."

> This seems sensible. However, version 6.1 did not have this
> restriction; for any ungrouped function not within an aggregate, the
> first matching value was returned. In effect, a default aggregate was
> applied.

Right. But that was contrary to the SQL spec, and furthermore the
results were not very predictable (since there's no good way to know
which tuple the executor will find first). So we changed it.

> Unfortunately, my application took advantage of this
> behavior. My basic question is, what's the easiest way to duplicate
> this behavior using v6.5.2?

The usual advice is

select x, min(y) from table group by x;

but this depends on the existence of a min() function for the datatype
of y. The approach you were trying depends on a '<' operator; neither
are normally provided for array types. (Although if the array component
type has a '<', it seems like it shouldn't be that hard to provide an
ordering operator for the array type... another TODO list item...)

What you can do at the moment is

select distinct on x x, y from table;

This is nonstandard, klugy, and just as unpredictable as the old GROUP
BY behavior, but you only need an ordering operator on x.

BTW, in 7.0 the syntax will be

select distinct on (x) x, y from table;

per recent discussion in this list.

regards, tom lane