Re: Bringing other columns along with a GROUP BY clause

"Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> writes:
> I have a table that has three interesting columns: coil_id, charge, and
> coldspot_time. A charge can have several coils, so there are several
> records with differing coil_ids but the same charge. I want a list of
> the coils whose coldspot_times are the largest for their charge.

If you don't mind a Postgres-ism, the DISTINCT ON clause provides a
reasonably efficient approach for this type of problem. See the
"weather reports" example in the SELECT reference page.

You could also do something like

select coil_id, charge, coldspot_time
from inventory i
where coldspot_time = (select max(coldspot_time) from inventory i2
where i2.charge = i.charge);

which doesn't require any nonstandard features, but on the other hand
its performance will probably suck if charge groups are large (and
even if they aren't, it'll suck without an index on charge). Also note
that if there are several coils sharing the max in a charge group, this
will show you all of them, which might or might not be what you want.
(The DISTINCT ON method gets you only one maximum per group, but you
can sort on additional columns to prioritize which one to show.)

[ thinks for a bit... ] Another spec-compliant variant is

select coil_id, charge, coldspot_time
from inventory
where (charge, coldspot_time) in (select charge, max(coldspot_time)
from inventory group by charge);

which should work better than the previous for large charge groups.
This also gives you all the maxima per group.

I have a feeling that it could also be done via the "window functions"
feature due to be introduced in 8.4, but I'm not entirely sure how.
Anybody feeling handy with those?

regards, tom lane