Re: writing a MIN(RECORD) aggregate

On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote:
> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
> > SELECT i, (MIN((j,k))).k
> > FROM tbl
> > GROUP BY i;
>
> How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ?

Because I want the value of k associated with the minimum value of j.
For example, if I have data looking like:

i j k
1 3 7
1 4 8
2 5 10
2 6 9

I want to get this out:

i k
1 7
2 10

I would get this if I used the DISTINCT ON or if MIN was valid over
records. With your code I'd get this:

i k
1 7
2 9

> I'm not saying that min/avg/max/etc(RECORD) wouldn't be useful;

AVG wouldn't work, because it relies on treating it's parameter as a
numeric field over which summation and division are valid operations.
MIN/MAX just relies on there being a (total) ordering operator available
and with PG there pretty much always is.

> I'm just failing to see the use in these examples.

Did the example above make things any clearer?

I've also just realised that PG's current handling of NULLs inside
records is also going to cause problems. The main problem seems to be
that the IS NULL operator isn't consistent with comparison operators.
For example:

(1,NULL) IS NULL --> FALSE
(1,NULL) = (1,NULL) --> NULL

I'm not sure if it's just my intuition is off, or whether there is an
invariant (e.g. a comparison returns NULL if-and-only-if either side
evaluate TRUE to IS NULL) that's being broken.

Thanks,
Sam