Re: writing a MIN(RECORD) aggregate

On Tue, Mar 25, 2008 at 06:58:06PM +0000, Gregory Stark wrote:
> "Sam Mason" <sam(at)samason(dot)me(dot)uk> writes:
> > SELECT i, MIN(k) OVER (PARTITION BY j)
> > FROM tbl
> > GROUP BY i;
> >
> > This is obviously wrong, but I don't see how to get to where I need to
> > be.
>
> I'm not entirely sure myself. I think it might involve RANK OVER j though.

The main thing I wanted to avoid was an explosion of sub-queries that
you get with DISTINCT ON style queries. For example, with record style
syntax, I can do:

SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
FROM tbl
GROUP BY i;

whereas using DISTINCT ON I'd have to do:

SELECT a.i, a.k AS ka, b.k as kb
FROM (
SELECT DISTINCT ON (i) i, k
FROM tbl
ORDER BY i, j) a, (
SELECT DISTINCT ON (i) i, k
FROM tbl
ORDER BY i, mycode(j)) b
WHERE a.i = b.i;

Which gets unmanageable quickly. Any idea how window functions would
cope with this sort of complexity? Or is this what you meant by:

> I suspect it will look more like the DISTINCT ON solution than the min(record)
> solution.

Thanks,
Sam