sql - Removing Self Join -
i have below query
select h.* table1 h left join table1 e on e.fundno = h.fundno , e.trantype = 'd' , e.modifiedon > h.modifiedon e.fundno null , h.trantype != 'd'
is there way avoid self join. know can rewritten using not exists
trying avoid hitting table twice..
if trantype
same can use row_number
this.. since trantype
different couldn't find way it..
you seem want non-d rows there no "d" row modifed @ laster time. use window functions:
select h.* (select h.*, max(case when h.transtype = 'd' modifiedon end) on (partition fundno) last_d_modifiedon table1 h ) h (last_d_modifiedon null or last_d_modifiedon < modifiedon) , h.transtype <> 'd';
Comments
Post a Comment