CREATE TABLE family ( family_id, last_name, first_name) AS
SELECT 111, 'Johnson', 'Ann' FROM DUAL UNION ALL
SELECT 111, 'Johnson', 'Lisa' FROM DUAL UNION ALL
SELECT 111, 'Johnson', 'Dave' FROM DUAL UNION ALL
SELECT 222, 'Adams', 'Paul' FROM DUAL UNION ALL
SELECT 222, 'Adams', 'Jack' FROM DUAL UNION ALL
SELECT 333, 'Clark', 'Danny' FROM DUAL UNION ALL
SELECT 333, 'Clark', 'Kelly' FROM DUAL UNION ALL
SELECT 333, 'Clark', 'Jill' FROM DUAL UNION ALL
SELECT 444, 'Hutton', 'Carter' FROM DUAL;


CREATE TABLE family_plan ( family_id, family_plan_id, family_plan_date) AS
SELECT 111, 1, DATE '2013-01-01'
FROM DUAL UNION ALL
SELECT 111, 2, DATE '2020-01-02'
FROM DUAL UNION ALL
SELECT 111, 3, DATE '2018-02-04'
FROM DUAL UNION
SELECT 222, 1, DATE '2022-01-01'
FROM DUAL UNION ALL
SELECT 222, 2, DATE '2010-01-03'
FROM DUAL UNION ALL
SELECT 333, 2, DATE '2013-01-01'
FROM DUAL UNION
SELECT 333, 3, DATE '2020-01-02'
FROM DUAL UNION
SELECT 333, 4, DATE '2018-02-04'
FROM DUAL UNION
SELECT 333, 5, DATE '2022-01-01'
FROM DUAL UNION
SELECT 444, 1, DATE '2010-01-03'
FROM DUAL UNION
SELECT 444, 2, DATE '2020-01-01'
FROM DUAL;
/
 
select f.family_id,  f.last_name,  f.first_name, max max(fp.family_plan_id), fp.family_plan_date

from family f
         family_plan fp
        join family f on f.family_id = fp.family_id

SQL> with data as (
  2    select f.family_id,  f.last_name,  f.first_name, fp.family_plan_id, fp.family_plan_date,
  3           row_number() over (partition by f.family_id order by fp.family_plan_id desc) rn
  4    from family f, family_plan fp
  5    where f.family_id = fp.family_id
  6  )
  7  select family_id,  last_name,  first_name, family_plan_id, family_plan_date
  8  from data
  9  where rn = 1
 10  order by 1
 11  /
 FAMILY_ID LAST_NA FIRST_ FAMILY_PLAN_ID FAMILY_PLAN
---------- ------- ------ -------------- -----------
       111 Johnson Dave                3 04-FEB-2018
       222 Adams   Jack                2 03-JAN-2010
       333 Clark   Jill                5 01-JAN-2022
       444 Hutton  Carter              2 01-JAN-2020