On Tue, Mar 25, 2008 at 06:58:06PM +0000, Gregory Stark wrote: > "Sam Mason" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers