Re: retrieving primary key for row with MIN function

Sean Davis wrote:
>     I need to retrieve PK (r.id <http://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 <http://h.id> AS host_id, MIN(r.start_date) AS
>     reservation_start_date,
>     r.id <http://r.id> AS reservation_id
>     FROM hosts h
>     LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id <http://h.id>
>     LEFT OUTER JOIN reservation r ON r.id <http://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 <http://h.id>, r.id <http://r.id>
>
>
> HAVING r.start_date=MIN(r.start_date);
>
> Does that do what you need?

Unfortunately, it doesn't: if I add HAVING
r.start_date=MIN(r.start_date), Postgres complains:

ERROR:  column "r.start_date" must appear in the GROUP BY clause or be
used in an aggregate function

Which means I have to add it to GROUP BY clause. Which means that
separate records for r.start_date (for many reservations of a given
host) appear:

     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, r.start_date
     HAVING r.start_date = MIN(r.start_date)
   ORDER BY h.id


host_id    reservation_start_date    reservation_id
344     2009-05-11            5
344     2009-05-18              6
344     2009-05-29              7


Regards,
mk