Re: Bringing other columns along with a GROUP BY clause

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Bringing other columns along with a GROUP BY clause
Date: 2009-02-05 20:07:42
Message-ID: 20090205200742.GN3008@frubble.xen.chris-lamb.co.uk
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general

[ Rob, it would help if you didn't "reply" to unrelated messages.
Decent mail programs automatically "thread" emails based on what you
reply to and hence unrelated messages like yours tend to get lost. ]

On Thu, Feb 05, 2009 at 02:43:07PM -0500, Rob Richardson wrote:
> I want a list of
> the coils whose coldspot_times are the largest for their charge.

> Select coil_id, charge, max(coldspot_time)
> From inventory
> Group by charge

The DISTINCT ON clause would help here, something like:

SELECT DISTINCT ON (coil_id), coil_id, charge, MAX(largest_time)
FROM inventory
GROUP BY coil_id, charge
ORDER BY coil_id, MAX(largest_time) DESC;

--
Sam http://samason.me.uk/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-02-05 21:12:24 Re: Bringing other columns along with a GROUP BY clause
Previous Message Rob Richardson 2009-02-05 19:43:07 Bringing other columns along with a GROUP BY clause