sql - Find subsequent records that aren't active -

i've got table of dd information , need select records cancelled don't have subsequent active record

i have tried

select      d1.ddireference  ddi d1 left outer join ddi d2 on (d1.ddireference = d2.ddireference , d1.transactionid < d2.transactionid) inner join [tm113 file jo check] tm on tm.[ddi reference] = d1.ddireference      d1.ddstatus = 'can' , d2.ddstatus <> 'act' 

but return me ddireference of record cancelled, cancelled, cancelled, active.

i want return records cancelled no active.

thanks

darren

i'm not entirely sure understanding question correctly, query gets cancelled records no active record higher transactionid exists. note original query, if multiple records status can exist, 1 row in results each of records. use select distinct avoid in case.

select d1.ddireference  ddi d1 inner join [tm113 file jo check] tm on tm.[ddi reference] = d1.ddireference d1.ddstatus = 'can' , not exists(     select *     ddi d2     d2.ddireference = d1.ddireference     , d2.transactionid > d1.transactionid     , d2.ddstatus = 'act')