Re: retrieving primary key for row with MIN function

From: daq <daq(at)ugyvitelszolgaltato(dot)hu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: retrieving primary key for row with MIN function
Date: 2009-04-29 15:41:52
Message-ID: 146186391968.20090429174152@ugyvitelszolgaltato.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


MK> Hello everyone,

MK> I need to retrieve PK (r.id in the query) for row with
MK> MIN(r.start_date), but with a twist: I need to select only one record,
MK> the one with minimum date.

MK> Doing it like this does not solve the problem:

MK> SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date,
MK> r.id AS reservation_id
MK> FROM hosts h
MK> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
MK> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
MK> (r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
MK> GROUP BY h.id, r.id
MK> ORDER BY reservation_start_date ASC

MK> I have to use either GROUP BY r.id or use MIN(r.id). MIN(r.id) doesn't
MK> select the id from the row with corresponding MIN(r.start_date), so it's
MK> useless, while GROUP BY r.id produces more than one row:

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

MK> I need to select only row with reservation_id = 38.

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

MK> Regards,
MK> mk

I think, the key is:
SELECT id FROM reservation WHERE start_date=(íselect min(start_date)
FROM reservation);

You got the id. Use it well! :)

DAQ

Browse pgsql-novice by date

  From Date Subject
Next Message Adam Ruth 2009-04-29 22:20:38 Re: retrieving primary key for row with MIN function
Previous Message Marcin Krol 2009-04-29 15:19:48 Re: retrieving primary key for row with MIN function