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
Advertisement
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) );