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

# Max Age of Children with Subselects?

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?
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?
Currently Being Moderated
Post what you have tried so far. I will give you a clue on how to solve this .

Regards

Raj
• ###### 3. Re: Max Age of Children with Subselects?
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?
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?
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?
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?
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?
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