get the values mit highets value sql

the following table is given (in Oracle);

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

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