Re: retrieving primary key for row with MIN function

Out of curiosity I ran some quick and dirty tests. It looks like
either can be as much as twice as fast as the other, depending on the
data and query, so you'd want to runs some tests before picking a
method.

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