SQL to return one row from many

I have the following data

PersonId	City	Type	UpdateDate
123	Boston	P	01/01/2021
123	Boston	M	02/01/2021
130	Detroit	P	01/01/2021
130	Detroit	M	03/01/2021
140	Dallas	M	02/01/2021
140	Dallas	M	03/01/2021

I want a query that returns one row per PersonId. If the Type is “P” return that row otherwise return the row with the minimum UpdateDate. So the query would return:

PersonId	City	Type	UpdateDate
123	Boston	P	01/01/2021
130	Detroit	P	01/01/2021
140	Dallas	M	02/01/2021

In the past I would write a query like

select * from person, address
where person.PersonId = address.PersonId
group by PersonId
having (Type = 'P') or (UpdateDate = min(UpdateDate))

but this is not allowed anymore.

What should my SQL query be in SQL Server?

Answer

Presumably you want the most recent address per person. If so, outer apply is very well suited to this problem:

select p.* a.*
from person p outer apply
     (select top (1) a.*
      from address a
      where a.PersonId = p.PersonId
      order by (case when a.type = 'P' then 1 else 2 end),
               a.updatedate desc
     ) a;

No aggregation is called for.