Re: Bringing other columns along with a GROUP BY clause - Mailing list 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/