[SQL] How to remove max sub query to make query optimisation in sql query

I have 100 store procedure calling same like below max query I dont want max query because it make query to be slower and as my query is below of 2 line but actually my query having many join but need to remove sub query, also i have set index but need to remove max sub query

select *
from xyz d
inner join (
select max(Id)AS Id,personId
from xyz (NOLOCK)
where category=1
group by personId
) dm on d.Id=dm.Id


Reference: Optimizing multiple joins

the above reference explaining about pivot way to solve this max subqery but dont know or other way to do optimize my max query, i dont want in cte or any subqueries

Continue reading...