CODEWITHSUNDEEP
sqlsql-servert-sql
Karl
Karl

Reputation: 53

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?

Upvotes: 0

Views: 476

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 3

Related Questions