How to remove max sub query to make query optimisation in SQL query

I have 100 stored procedures calling same like below max query.

I don’t want max query because it make query to be slower, and as my query is below of 2 lines, but actually my query having many joins 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 don’t know or other way to do optimize my max query, I don’t want in cte or any subqueries

Question Max query and Joel Coehoorn Query Execution Plan

You need to sign in to view this answers