Pulling different rows based on age in select query



Context

I am using Oracle SQL and have data that looks like this. People are asked every year what their favorite movie is and that may update from year to year.

Person        Date_of_Birth        Favorite_Movie      Update_Date
Bob             08-10-1950            Endgame             2021-01-01
Bob             08-10-1950            Batman              2020-01-01
Peter           11-01-1990            Toy Story           2021-01-01
Peter           11-01-1990            Mulan               2020-01-01

Problem

I want to write a query so that:

if DOB >= 70 then pull the Favorite Movie from the year they turned 70.
else: extract movie from most current year 

So for Peter and Bob, the desired outcome would look like:

Person            DOB            Favorite_Movie      Update_Date
Bob             08-10-1950            Batman              2020-01-01
Peter           11-01-1990            Toy Story           2021-01-01

Current code

My current query looks like this:

SELECT *,
CASE 
WHEN trunc(months_between(sysdate,Date_Of_Birth)/12) >= 70 THEN

Answer

One method uses a correlated subquery:

select f.*
from favorites f
where f.update_date = (select max(f2.update_date)
                       from favorites f2
                       where f2.person = f.person and
                             f2.date_of_birth >= add_months(f.update_date, - 12 * 70)
                      );


Source: stackoverflow