重写查询以删除tsql中的内部查询以进行优化

I am trying to optimize the following query, According to execution plan, the sort in the inner query has a high cost. could the following query be re-written so that its easy to read and performs well?I am trying to optimize the following query, Ac