CODEWITHSUNDEEP
sqlsql-servert-sqlself-join
Pரதீப்
Pரதீப்

Reputation: 93744

Removing Self Join

I have the below query 

SELECT h.*
FROM   table1 h
       LEFT JOIN table1 e
              ON e.fundno = h.fundno 
                 AND e.trantype = 'D'
                 AND e.modifiedon > h.modifiedon
WHERE  e.fundno IS NULL
       AND h.trantype != 'D'

Is there way to avoid the self join. I know it can be rewritten using Not Exists but I am trying to avoid hitting the table twice..

If the trantype is same then we can use Row_Number to do this.. since trantype is different I couldn't find a way to do it..

Upvotes: 0

Views: 296

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You seem to want non-D rows where there is no "D" row modifed at a laster time. You could use window functions:

select h.*
from (select h.*,
             max(case when h.transtype = 'D' then modifiedon end) over (partition by fundno) as last_d_modifiedon
      from table1 h
     ) h
where (last_d_modifiedon is null or last_d_modifiedon < modifiedon) and
      h.transtype <> 'D';

Upvotes: 2

Related Questions