Skip to content
Advertisement

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?

Advertisement

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement