Is there something you are not showing us? With what is given I don't see why you are trying to do with one sql statement. Just separate into individual statements as you are not saving any time. Also what are you joining on? Did you mean select * from reductions, surprimes?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Wednesday, February 16, 2011 8:57 AM
To: Midrange Systems Technical Discussion
Subject: SQL can I do this without 2 CTE?
Hi,
I have a transaction table with different rates that may be indicated by a flag as either a discount(reduction) or a premium(surprime).
I need the maximum of each type of rate.
I'm using
with
reductions as ( select max(rate) as max_reduction from mytable where reductionFlag = '1'),
surprimes as ( select max(rate) as max_surprime from mytable where surprimeFlag ='1')
select * from reductions join surprimes
;
But I wonder if there's a simpler way?
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
