pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing min|max in where
2+ messages / 2 participants
[nested] [flat]
* using min|max in where
@ 2010-08-25 00:41 Ben Carbery <ben.carbery@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ben Carbery @ 2010-08-25 00:41 UTC (permalink / raw)
To: pgsql-sql
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.
B
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: using min|max in where
@ 2010-08-25 14:46 Bruce Momjian <bruce@momjian.us>
parent: Ben Carbery <ben.carbery@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2010-08-25 14:46 UTC (permalink / raw)
To: Ben Carbery <ben.carbery@gmail.com>; +Cc: 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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-25 14:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-25 00:41 using min|max in where Ben Carbery <ben.carbery@gmail.com>
2010-08-25 14:46 ` Bruce Momjian <bruce@momjian.us>
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