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

Re: writing a MIN(RECORD) aggregate

From: Decibel! <decibel(at)decibel(dot)org>
To: Sam Mason <sam(at)samason(dot)me(dot)uk>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: writing a MIN(RECORD) aggregate
Date: 2008-04-02 23:05:39
Message-ID: F4A45BEA-1FF3-48A3-A055-6EE0C3A4EED0@decibel.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mar 25, 2008, at 11:33 AM, Sam Mason wrote:
> On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote:
>> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
>>>  SELECT i, (MIN((j,k))).k
>>>  FROM tbl
>>>  GROUP BY i;
>>
>> How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ?
>
> Because I want the value of k associated with the minimum value of j.


Ahh, makes sense. FWIW...

SELECT i, (SELECT k FROM ... WHERE i = i.i ORDER BY j LIMIT 1)
     FROM (SELECT DISTINCT i FROM ...) i
;

If you needed more than just k, I think there's a way to do that in  
the FROM clause, too.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

pgsql-hackers by date

Next:From: Marc G. FournierDate: 2008-04-02 23:18:03
Subject: Re: Several tags around PostgreSQL 7.1 broken
Previous:From: Tom LaneDate: 2008-04-02 23:04:09
Subject: Re: [GENERAL] ANALYZE getting dead tuple count hopelessly wrong

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