the following table is given (in Oracle);
x
LVL KID FATHER
1 POD001 DPR001
1 POD002 DPR002
1 POD003 POD002
2 POD003 DPR002
2 POD004 DPR001
1 POD004 POD001
2 POD005 POD002
1 POD005 POD003
3 POD005 DPR002
I want to get for all kids the father. If the there are more than one father (POD003, POD004 and POD005), take the value with the highest value from LVL column.
Result must be like this;
LVL KID FATHER
1 POD001 DPR001
1 POD002 DPR002
2 POD003 DPR002
2 POD004 DPR001
3 POD005 DPR002
Thank you for your help.
Regards Serdar
Advertisement
Answer
You group by kid and get the maximum level and join to the main table:
select t.*
from tablename t inner join (
select kid, max(lvl) maxlvl
from tablename
group by kid
) g
on g.kid = t.kid and g.maxlvl = t.lvl
order by t.kid
See the demo
or with NOT EXISTS:
select t.*
from tablename t
where not exists (
select 1 from tablename
where kid = t.kid and lvl > t.lvl
)
order by t.kid
See the demo