From: Bruce Momjian <bruce@momjian.us>
To: Ben Carbery <ben.carbery@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: using min|max in where
Date: Wed, 25 Aug 2010 10:46:11 -0400 (EDT)
Message-ID: <201008251446.o7PEkBJ21094@momjian.us> (raw)
In-Reply-To: <AANLkTinbg96cBzS+w31o75=wYUmHZxmwUJh4bKQc1Az2@mail.gmail.com>
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. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruce@momjian.us, ben.carbery@gmail.com
Subject: Re: using min|max in where
In-Reply-To: <201008251446.o7PEkBJ21094@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox