Thread: retrieving primary key for row with MIN function

On Wed, Apr 29, 2009 at 6:20 PM, Adam Ruth <adamruth@mac.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On Wed, Apr 29, 2009 at 6:20 PM, Adam Ruth <adamruth@mac.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice