In SQL how do I select based on a timestamp only if another criterion exists

Question

I don't even know how to ask this question. I have a table with names addresses type of address and a timestamp on when the address was added. Each person may have multiple addresses of each type.

Name   Addr  Type   Timestamp
a      abc   home   Jan 5 2022
a      def   home   Jul 5 2021
a      ghi   mail   Mar 3 2022

For example in the above scenario, I want to select the newest home address if it is available. If not, I want to select the newest mailing address. There are other address types too and I like work and alternate mailing address. I have an order of preference of the address type. And from that I want the newest address. In the table above I would want the first row even though the mailing address is newer.

I don't even know how to approach this problem so I don't know what to try. I would truly appreciate any suggestions.

Answer 1

Assuming your RDBMS supports analystic window functions you would use a typical row_number solution:

with address as (
  select *,
    row_number() over(partition by Name order by Type, Timestamp desc) rnk
)
select *
from address
where rnk = 1;

If the desired ordering of type is not alphabetic you can introduce your own ordering using a case expression, eg

order by case Type when 'home' then 1 when 'email' then 2... end

Even better, normalise the table such that the type is stored separately and use its foreign key for ordering, ensuring the list of types are assigned a key in priority order.