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

Re: retrieving primary key for row with MIN function

From: Adam Ruth <adamruth(at)mac(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: retrieving primary key for row with MIN function
Date: 2009-04-30 04:11:24
Message-ID: D2F28929-9985-46C1-B9C2-057777A2F72E@mac.com (view raw or flat)
Thread:
Lists: pgsql-novice
He said he'd rather not do a subquery because of performance. Though,  
it may actually be faster than the aggregate.

On 30/04/2009, at 12:28 PM, Sean Davis wrote:

>
>
> On Wed, Apr 29, 2009 at 6:20 PM, Adam Ruth <adamruth(at)mac(dot)com> wrote:
> Without windowing functions, I'm not sure of a built in method. But  
> you could create your own aggregate.
>
> Seems like you should be able to do this with a correlated subquery  
> that does an order and limit 1 and not have to use a custom aggregate?
>
> Sean
>
>
> ------------------------
> create type top_id_type as (id int, date timestamp);
>
> create or replace function top_id_state (	state top_id_type, id int,  
> date timestamp) returns top_id_type as $$
> declare
> 	result top_id_type;
> begin
> 	if state is null or date < state.date then
> 		result.id = id;
> 		result.date = date;
> 	else
> 		result = state;
> 	end if;
> 	return result;
> end
> $$ language plpgsql;
>
> create or replace function top_id_final (state top_id_type) returns  
> int as $$
> begin
> 	return state.id;
> end
> $$ language plpgsql;
> 	
> create aggregate top_id (int, timestamp) (
> 	sfunc = top_id_state,
> 	stype = top_id_type,
> 	finalfunc = top_id_final
> );
> 	
> -----------------------
>
> This is my first ever user defined aggregate, so someone may be able  
> to improve it.
>
> ------------------------- Usage
>
> SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date,
> top_id(r.id, r.start_date) AS reservation_id
>
> FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
> (r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date,  
> '2010-04-29'::date)
> GROUP BY h.id
> ORDER BY reservation_start_date ASC
>
> -------------------------
>
> On 29/04/2009, at 10:37 PM, Marcin Krol wrote:
>
>> Hello everyone,
>>
>> I need to retrieve PK (r.id in the query) for row with
>>
>> MIN(r.start_date), but with a twist: I need to select only one  
>> record,
>> the one with minimum date.
>>
>> Doing it like this does not solve the problem:
>>
>> SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date,
>> r.id AS reservation_id
>> FROM hosts h
>> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
>> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
>> (r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date,  
>> '2010-04-29'::date)
>> GROUP BY h.id, r.id
>> ORDER BY reservation_start_date ASC
>>
>> I have to use either GROUP BY r.id or use MIN(r.id). MIN(r.id)  
>> doesn't
>> select the id from the row with corresponding MIN(r.start_date), so  
>> it's
>> useless, while GROUP BY r.id produces more than one row:
>>
>> host_id	reservation_start_date	reservation_id
>> 361     2009-05-11              38
>> 361     2009-05-17              21
>>
>> I need to select only row with reservation_id = 38.
>>
>> I would rather not do subquery for every 'host' record, since there  
>> can
>> be a lot of them...
>>
>> Regards,
>> mk
>>
>>
>>
>>
>> -- 
>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>
>

In response to

pgsql-novice by date

Next:From: Adam RuthDate: 2009-04-30 05:39:47
Subject: Re: retrieving primary key for row with MIN function
Previous:From: Sean DavisDate: 2009-04-30 02:28:54
Subject: Re: retrieving primary key for row with MIN function

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