How to combine MIN and MAX inside a WHERE condition

Question

I have this query which requires to find a MAX value given certain description

select * from SCA_FXDFWD_REP S
where S.M_TRADE_NUM in  (SELECT MAX(S_AUX.M_TRADE_NUM)
                         FROM SCA_FXDFWD_REP S_AUX
                         WHERE S_AUX.M_ORIGIN_ID= S.M_ORIGIN_ID
                         AND S_AUX.M_SCA_DESC= 'ALTA'
                         GROUP BY  S_AUX.M_SCA_ORIG, S_AUX.M_TRADE_DAT)

And that works fine, but now I need to add another condition which requires to find de MIN for another similar condition like this:

SELECT * FROM SCA_FXDFWD_REP S
WHERE M_TRADE_NUM IN ((SELECT MIN(M_TRADE_NUM) FROM SCA_FXDFWD_REP S_AUX 
where S_AUX.M_ORIGIN_ID = S.M_ORIGIN_ID
AND S_AUX.M_SCA_DESC= 'UTILIZACION'))

I've tried AND, OR, UNION, and all kind of combinations in my knowledge to combine both conditions and get MAX when DESC='ALTA' and MIN when DESC='UTILIZACION' with no luck, I usually get an error where the prefix S won't match, etc.

Can anybody help on what would be the best way to combine this two condition and get only both MIN and MAX at the same time

Thanks,

Answer 1

use or in where condition

select * from SCA_FXDFWD_REP S
where S.M_TRADE_NUM =  (SELECT MAX(S_AUX.M_TRADE_NUM)
                         FROM SCA_FXDFWD_REP S_AUX
                         WHERE S_AUX.M_ORIGIN_ID= S.M_ORIGIN_ID
                         AND S_AUX.M_SCA_DESC= 'ALTA'
                         GROUP BY  S_AUX.M_SCA_ORIG, S_AUX.M_TRADE_DAT)
or S.M_TRADE_NUM = ((SELECT MIN(M_TRADE_NUM) FROM SCA_FXDFWD_REP S_AUX 
where S_AUX.M_ORIGIN_ID = S.M_ORIGIN_ID
AND S_AUX.M_SCA_DESC= 'UTILIZACION'))