From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201008251446.o7PEkBJ21094@momjian.us>
Subject: Re: using min|max in where
In-Reply-To: <AANLkTinbg96cBzS+w31o75=wYUmHZxmwUJh4bKQc1Az2@mail.gmail.com>
To: Ben Carbery <ben.carbery@gmail.com>
Date: Wed, 25 Aug 2010 10:46:11 -0400 (EDT)
CC: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Ben Carbery wrote:
> Hi, I have some sql like so:
> 
> SELECT min(date) INTO d FROM interest_rate WHERE m_code = NEW.code;
> UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate
> WHERE m_code = NEW.code AND date = d;
> 
> Actually this is pgsql but I don't think that matters.
> 
> I am wondering if I can make this more compact by somehow including the
> 'min' function in the WHERE clause, but WITHOUT simply moving the select in
> there. So not this..
> 
> UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate
> WHERE m_code = NEW.code AND date = (SELECT min(date) FROM interest_rate
> WHERE m_code = NEW.code);
> 
> This is just an example but I seem to find this pattern a lot in my
> functions.

I think you want the HAVING clause of SELECT.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +