Re: writing a MIN(RECORD) aggregate

"Sam Mason" <sam(at)samason(dot)me(dot)uk> writes:

> On Tue, Mar 25, 2008 at 06:58:06PM +0000, Gregory Stark wrote:
> 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:
...
> 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.

The flip side is that if you want to get several fields based on min(j) the
min(record) approach requires you to write that expression several times (and
the database to calculate it several times).

I think the window functions might (assuming an ideal implementation) get the
best of both worlds. You would be able to do something with multiple
partitions so you could ask of a few columns where rank over j = 1 and a few
more columns where rank over k = 1.

But, uh, I'm not sure. I'll have to sit down with the spec and see if that's
true. Furthermore it may be wishful thinking to hope that the implementation
will do anything special with the special case where you're only selecting
records where rank = 1.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!