This discussion is archived
8 Replies Latest reply: Sep 17, 2010 4:34 AM by 789895 RSS

Max Age of Children with Subselects?

745835 Newbie
Currently Being Moderated
Hi,

I have a really urgent problem, I've been working on since hours.
There's a table with Parent, Child, and ChildAge. And I want to have a Result-Table with all Parents and for each Parent the oldest Child and its Age.

DB-Table:

Parent | Child | ChildAge
----------------------------------
A | K | 2
A | L | 7
A | M | 13
A | N | 5
A | O | 22
B | P | 7
B | Q | 5



Result should be:

Parent | Child | ChildAge
----------------------------------
A | O | 22
B | P | 7


A solution with subselects or a With-Select-Statement is also ok,

Thanks Alex
  • 1. Re: Max Age of Children with Subselects?
    Karthick_Arp Guru
    Currently Being Moderated
    Try this.
    select parent, child, childage
      from (
    select parent, child, childage, rank() over(partition by parent order by childage desc) rk
      from table1
           )
     where rk = 1
  • 2. Re: Max Age of Children with Subselects?
    795160 Explorer
    Currently Being Moderated
    Post what you have tried so far. I will give you a clue on how to solve this .

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions065.htm#i1000901

    Regards

    Raj
  • 3. Re: Max Age of Children with Subselects?
    Kanish Journeyer
    Currently Being Moderated
    use this
    select *
    from(
    select parent,child,age,rank() over (partition by parent order by age desc) rnk
    from(
    (select 'A' Parent,'K' child,2 Age from dual union all
    select 'A' Parent,'L' child,7 Age from dual union all
    select 'A' Parent,'M' child,13 Age from dual union all
    select 'A' Parent,'N' child,5 Age from dual union all
    select 'A' Parent,'O' child,22 Age from dual union all
    select 'B' Parent,'P' child,7 Age from dual union all
    select 'B' Parent,'Q' child,5 Age from dual)
    ))
    where rnk = 1 
    kanish
  • 4. Re: Max Age of Children with Subselects?
    652379 Journeyer
    Currently Being Moderated
    Hi Alex,
    You can achieve the result using a hierarchical sql . What "Karthick_Arp" has put will definitely give you the result.

    Cheers
    Kanchana
  • 5. Re: Max Age of Children with Subselects?
    745835 Newbie
    Currently Being Moderated
    Thanks Karthick_Arp! Your Solution works fine.

    I forgot a little detail. If two children have the same age, then only one arbitrary child should be return, but I've fixed it on my own, like this (min, group by):

    select parent, min(child), childage
    from (
    select parent, child, childage, rank() over(partition by parent order by childage desc) rk
    from table1
    )
    where rk = 1
    group by parent, childage


    I think this is oracle specific. I there now solution with pure SQL?
  • 6. Re: Max Age of Children with Subselects?
    lee200 Pro
    Currently Being Moderated
    Try this:
    WITH t AS
    (SELECT 'A' parent, 'K' child, 2 age FROM dual UNION ALL
     SELECT 'A' parent, 'L' child, 7 age FROM dual UNION ALL
     SELECT 'A' parent, 'M' child, 13 age FROM dual UNION ALL
     SELECT 'A' parent, 'N' child, 5 age FROM dual UNION ALL
     SELECT 'A' parent, 'O' child, 22 age FROM dual UNION ALL
     SELECT 'B' parent, 'P' child, 7 age FROM dual UNION ALL
     SELECT 'B' parent, 'P' child, 7 age FROM dual UNION ALL
     SELECT 'B' parent, 'Q' child, 5 age FROM dual)
    SELECT DISTINCT *
    FROM   t
    WHERE (parent, age) IN (SELECT   parent,
                                     MAX(age)
                            FROM     t
                            GROUP BY parent)
  • 7. Re: Max Age of Children with Subselects?
    733928 Newbie
    Currently Being Moderated
    SELECT PARENT,CHILD,ChildAge FROM(
    SELECT PARENT,CHILD,ChildAge,RANK() OVER(partition BY PARENT ORDER BY CHILD) RANK FROM DB
    ) WHERE RANK='1'
  • 8. Re: Max Age of Children with Subselects?
    789895 Expert
    Currently Being Moderated
    Hi,

    Hope this might help.
    SELECT * FROM PARCHILD WHERE (PARENT,CHILD,AGE) IN (SELECT PARENT,MAX(CHILD), MAX(AGE) FROM PARCHILD GROUP BY PARENT)
    cheers

    VT

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points