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

Re: retrieving primary key for row with MIN function

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: retrieving primary key for row with MIN function
Date: 2009-04-30 12:14:03
Message-ID: gtc4mb$4o1$7@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2009-04-29, Marcin Krol <mrkafk(at)gmail(dot)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.

use LIMIT. 

eg:

SELECT
   h.id  AS host_id,
   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)
ORDER BY reservation_start_date ASC
LIMIT 1



In response to

pgsql-novice by date

Next:From: Fred MoseleyDate: 2009-05-01 17:24:32
Subject: Upgrading to Postgresql 8 on RHEL 3 (Release 9 Shrike)
Previous:From: Adam RuthDate: 2009-04-30 05:39:47
Subject: Re: retrieving primary key for row with MIN function

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