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