Thread: retrieving primary key for row with MIN function
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
On Wed, Apr 29, 2009 at 6:30 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 > ORDER BY reservation_start_date ASC > Couldn't you just use something like select r.id from (join tables here) order by date asc limit 1 or something like that?
On Wed, Apr 29, 2009 at 5:30 AM, Marcin Krol <mrkafk@gmail.com> 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
Could you end the query with a "LIMIT 1"? 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 ; On Wed, Apr 29, 2009 at 8:30 PM, 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 > 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 > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >