Re: writing a MIN(RECORD) aggregate

On Tue, Mar 25, 2008 at 06:58:06PM +0000, Gregory Stark wrote:
> "Sam Mason" <sam@samason.me.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
DISTINCTON (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