From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | Marcin Krol <mrkafk(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: retrieving primary key for row with MIN function |
Date: | 2009-04-29 13:08:21 |
Message-ID: | 264855a00904290608i2ce6de65t770a975fa417e6eb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Apr 29, 2009 at 8:37 AM, Marcin Krol <mrkafk(at)gmail(dot)com> 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
HAVING r.start_date=MIN(r.start_date);
Does that do what you need?
>
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2009-04-29 14:57:49 | Funny foreign keys |
Previous Message | Marcin Krol | 2009-04-29 12:37:50 | retrieving primary key for row with MIN function |