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-29 22:20:38
Message-ID: 2FEA1B56-B422-4F2E-9BA4-C5975F466E74@mac.com (view raw or flat)
Thread:
Lists: pgsql-novice
Without windowing functions, I'm not sure of a built in method. But  
you could create your own aggregate.

------------------------
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

Responses

pgsql-novice by date

Next:From: Rodrigo E. De León PlicetDate: 2009-04-30 02:04:52
Subject: Re: Funny foreign keys
Previous:From: daqDate: 2009-04-29 15:41:52
Subject: Re: retrieving primary key for row with MIN function

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