Re: SQL can I do this without 2 CTE?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You can do a self join....

Select A.*, b.*
from mytable A
         join mytable B
           on A.key = B.key
            and a.reductionFlag = '1'
            and B.surprimeFlag = '1'

An alternative might be
Select A.*, b.*
from mytable A
         join mytable B
           on A.key = B.key
WHERE
        a.reductionFlag = '1'
         and B.surprimeFlag = '1'

Including the criteria in the join gets a little tricky, but with two
indexes, should be faster...

I'd be surprised if there was any improvement over the CTE version,
and the CTE version is more self explanatory.

Charles



On Wed, Feb 16, 2011 at 8:57 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:
> 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.