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