From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Ben Carbery <ben(dot)carbery(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: using min|max in where |
Date: | 2010-08-25 14:46:11 |
Message-ID: | 201008251446.o7PEkBJ21094@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Igrishin | 2010-08-26 11:56:38 | Boolean output representation. |
Previous Message | Ben Carbery | 2010-08-25 00:41:21 | using min|max in where |