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.
x
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)
);