Thread: using min|max in where

Hi, I have some sql like so:<br /><br />SELECT min(date) INTO d FROM interest_rate WHERE m_code = NEW.code;<br />UPDATE
interest_rateSET date = NEW.start_date, rate = NEW.initial_rate WHERE m_code = NEW.code AND date = d;<br /><br />
Actuallythis is pgsql but I don't think that matters.<br /><br />I am wondering if I can make this more compact by
somehowincluding the 'min' function in the WHERE clause, but WITHOUT simply moving the select in there. So not
this..<br/><br />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);<br /><br />This is just an example but I seem to
findthis pattern a lot in my functions.<br /><br />B<br /><br />        <br /> 

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. +