Re: writing a MIN(RECORD) aggregate

On Mar 25, 2008, at 4:43PM, Gregory Stark wrote:
> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
> > SELECT i, (MIN((j,k))).k
> > FROM tbl
> > GROUP BY i;
>
> I have nothing against having min(record) and it does seem like it would let
> you do this at least for reasonably simple cases.

The main reason for this was that I've needed min(record) a few times
before and thought it would be reasonably easy to code.

> But I'm more eager to see full OLAP window functions which would let you do
> this and a whole lot else as well.

I've never used window functions before so don't think about them when
solving my problems. If they were available I'd probably start using
them. From the small bit of reading that I've done around them, they
seem very imperative in nature. I'm not sure if this is a good or a bad
thing.

In a database that did support them, how would I write my query with
them? My first naive attempt was this:

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.

Sam