retrieving primary key for row with MIN function

From: Marcin Krol <mrkafk(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: retrieving primary key for row with MIN function
Date: 2009-04-29 12:30:14
Message-ID: 49F84856.5010400@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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:

host_id reservation_start_date reservation_id
361 2009-05-11 38
361 2009-05-17 21

I need to select only row with reservation_id = 38.

I would rather not do subquery for every 'host' record, since there can
be a lot of them...

Regards,
mk

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2009-04-29 14:52:52 Connecting to a postgreSQL database with windows CE over wi-fi; failing gracefully
Previous Message Jasen Betts 2009-04-29 11:20:11 Re: UPDATE... FROM - will ORDER BY not respected?