Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group