Home » SQL & PL/SQL » SQL & PL/SQL » Oracle flinding max plan id (19.8)
Oracle flinding max plan id [message #685965] Tue, 10 May 2022 16:22 Go to next message
Unclefool
Messages: 47
Registered: August 2021
Member
I have 2 tables: family and family_plan. I'm trying to find the largest family_plan_id for each family.

The query below is pseudo code on how I'm looking to produce the output. Any help would be greatly appreciated.


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


Re: Oracle flinding max plan id [message #685966 is a reply to message #685965] Wed, 11 May 2022 00:24 Go to previous message
Michel Cadot
Messages: 68201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Previous Topic: partition non partioned table
Next Topic: Help me for a SQL query
Goto Forum:
  


Current Time: Sun Jun 26 03:48:30 CDT 2022