On Wed, Apr 29, 2009 at 5:30 AM, Marcin Krol <mrkafk@xxxxxxxxx> wrote:
> 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.
If you mean on row period then just add a limit 1 to the end of your
existing query.
> 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
LIMIT 1;
If you mean the minimum start_date for each group of r.id then try distinct on
> SELECT DISTINCT ON ( r.id ) h.id AS host_id, r.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)
> ORDER BY r.id, r.start_date;
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
