Return Most Recent Record group by 2 ID fields

I need to display the current status of a part (‘approved’ or ‘not approved’) I am setting up the status table as MaterialNo, ToolID, Status, Asofdate

the idea is I may have several tools approved and several not approved. I want to be able to see the most recent status for each tool but I don’t want to see the historical status.

have tried to use MAX(Asofdate) and group by MaterialNo, ToolID, and Status but it returns all of the status history for each.

SELECT MaterialNo, ToolID, PPAPStatus, MAX(Asof) as "AsOf"
  FROM [MfgDataCollector].[dbo].[PPAPStatus]
  Group By MaterialNo, ToolID, PPAPStatus

The Status Table has this data:

MaterialNo  ToolID  Status          Asofdate
52748677    1       Not approved 2019-10-10
52748677    1       approved    2019-10-13
52748677    2       approved    2019-10-14

I Want to see:

MaterialNo  ToolID  Status         Asofdate
52748677    1       approved    2019-10-13
52748677    2       approved    2019-10-14

Answer

One solution that usually has very good performance is a correlated subquery:

select p.*
from [MfgDataCollector].[dbo].[PPAPStatus] p
where p.asof = (select max(p2.asof)
                from [MfgDataCollector].[dbo].[PPAPStatus] p2
                where p2.MaterialNo = p.MaterialNo and
                      p2.ToolID = p.ToolID
               );

For optimal performance, you want an index on (MaterialNo, ToolID, asof).