Skip site navigation (1) Skip section navigation (2)

retrieving primary key for row with MIN function

From: Marcin Krol <mrkafk(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: retrieving primary key for row with MIN function
Date: 2009-04-29 12:37:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hello everyone,

I need to retrieve PK ( 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 AS host_id, MIN(r.start_date) AS reservation_start_date, AS reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id =
LEFT OUTER JOIN reservation r ON = rh.reservation_id AND
(r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
ORDER BY reservation_start_date ASC

I have to use either GROUP BY or use MIN( MIN( doesn't
select the id from the row with corresponding MIN(r.start_date), so it's
useless, while GROUP BY 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...



pgsql-novice by date

Next:From: Sean DavisDate: 2009-04-29 13:08:21
Subject: Re: retrieving primary key for row with MIN function
Previous:From: Jasen BettsDate: 2009-04-29 12:27:39
Subject: Re: Copying data from one table of one database to other table f other database

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group