CODEWITHSUNDEEP
sqloracle-databasedateif-statementcase
KL_
KL_

Reputation: 309

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

Upvotes: 0

Views: 63

Answers (2)

Littlefoot
Littlefoot

Reputation: 142958

If you sort rows by age (when 70 years of age has the "highest" rank) and then update date (so that most recent year comes to the "top"), then this might be one option:

SQL> select person, dob, favorite_movie, update_date
  2  from (select t.*,
  3        row_number() over
  4          (partition by person
  5           order by case when extract(year from update_date) - extract (year from dob) = 70 then 1 else 2 end,
  6                    update_date desc) rn
  7        from test t
  8       )
  9  where rn = 1;

PERSO DOB        FAVORITE_ UPDATE_DAT
----- ---------- --------- ----------
Bob   1950-10-08 Batman    2020-01-01
Peter 1990-01-11 Toy Story 2021-01-01

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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

Upvotes: 1

Related Questions