Re: writing a MIN(RECORD) aggregate

On Tue, Mar 25, 2008 at 07:54:17PM +0000, Gregory Stark wrote:
> "Sam Mason" <sam(at)samason(dot)me(dot)uk> writes:
> > SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
> > FROM tbl
> > GROUP BY i;
>
> 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).

No. My demos have only used one column because that's the smallest
useful demo.

SELECT i, r.k, r.l
FROM (
SELECT i, MIN((j,k,l)) AS r
FROM tbl
GROUP BY i) x;

The reason for the sub-select is only because SQL doesn't provide any
other way to name expressions. Hum, or at least this should work...
There doesn't seem to be any nice way of getting fields out of a record!

If I really want to do this, it's going to turn into quite an overhaul
of record handling in PG. It would also remove the nice syntactic trick
that a.b identifies the field "b" from table "a", and s.a.b means that
the above is in schema "s".

> 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.

I don't really understand what you're saying above. Optimisation is
another can of worms that shouldn't be opened until we know how this
sort of thing is going to be used.

Sam