From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Marcin Krol <mrkafk(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: retrieving primary key for row with MIN function |
Date: | 2009-05-01 03:09:27 |
Message-ID: | dcc563d10904302009x309eff60o47d661b441b98849@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 29, 2009 at 6:30 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
> ORDER BY reservation_start_date ASC
>
Couldn't you just use something like
select r.id from (join tables here) order by date asc limit 1
or something like that?
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2009-05-01 03:30:13 | Re: Understand this error |
Previous Message | Erick Papadakis | 2009-05-01 02:57:37 | Re: retrieving primary key for row with MIN function |