sql - How can i select rows for specific cells from same table?

Question

I have a table with tenants and their addresses.

A tenant can have several addresses and at each address can appear several times (closed, open, modified). The tenant appears first with an address (first) after which he can have several changes on the first address (closed, open, modified) or he can have other addresses (closed, open, modified).

How can I extract the date of closing the first address.

The problem come with a twist. The name of streets are not exactly like first addres. It can contain St., Ave. in their names.

The table look like this:

id	Tenant	code	Street	Number	Date
1	Alice	First	Abbey	5	01.01.2021
2	Alice	Modify	Abbey Ro.	5	02.01.2021
3	Alice	Open	Elm St	3	02.01.2021
4	Alice	Close	St. Abbey	5	05.01.2021
5	Bob	First	Fifth	10	01.02.2021
6	Bob	Open	Fifth Ave	222	01.02.2021
7	Bob	Close	Fifth Ave	222	05.02.2021
8	Bob	Close	Ave Fifth	10	06.02.2021

Answer 1

I would do it like this:

with cte as(
select tenant, code, street, number, date, row_number() over (partition by tenant order by date desc) as rank
from tenants
order by date desc
)
select * from cte where rank = 1;