On Wed, Apr 29, 2009 at 8:37 AM, Marcin Krol <mrkafk@gmail.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:
