mysql - How to give RANKs based on employee hierarchy in emp table -

i have emp table , king ceo of company, not have manager. king should rank '1'. king manager jones,clark,blake. these rank '2'....etc.

intermediate table emp , manager relation:

empno   ename   mgr 7566    jones   king 7782    clark   king 7698    blake   king 7900    james   blake 7844    turner  blake 7654    martin  blake 7521    ward    blake 7499    allen   blake 7934    miller  clark 7902    ford    jones 7788    scott   jones 7876    adams   scott 7369    smith   ford 7839    king    null 

final output should like:

mgr   rank     king    1 jones   2 clark   2 blake   2 james   3 turner  3 martin  3 ward    3 allen   3 miller  3 scott   3 ford    3 adams   4 smith   4 

thanks in advance. want done in oracle.

it took me while wrap head around heirarchy queries in past ... however, once take time fiddle them, aren't tough.

  sql> w_data (     2           select 7566 empno, rtrim('jones ') ename, rtrim('king ') mgr dual union     3           select 7782 empno, rtrim('clark ') ename, rtrim('king ') mgr dual union     4           select 7698 empno, rtrim('blake ') ename, rtrim('king ') mgr dual union     5           select 7900 empno, rtrim('james ') ename, rtrim('blake') mgr dual union     6           select 7844 empno, rtrim('turner') ename, rtrim('blake') mgr dual union     7           select 7654 empno, rtrim('martin') ename, rtrim('blake') mgr dual union     8           select 7521 empno, rtrim('ward  ') ename, rtrim('blake') mgr dual union     9           select 7499 empno, rtrim('allen ') ename, rtrim('blake') mgr dual union    10           select 7934 empno, rtrim('miller') ename, rtrim('clark') mgr dual union    11           select 7902 empno, rtrim('ford  ') ename, rtrim('jones') mgr dual union    12           select 7788 empno, rtrim('scott ') ename, rtrim('jones') mgr dual union    13           select 7876 empno, rtrim('adams ') ename, rtrim('scott') mgr dual union    14           select 7369 empno, rtrim('smith ') ename, rtrim('ford ') mgr dual union    15           select 7839 empno, rtrim('king  ') ename, null           mgr dual    16           )    17  select empno, ename, mgr, level    18    w_data    19   connect mgr = prior ename    20   start mgr null   /    21        empno ename  mgr        level   ---------- ------ ----- ----------         7839 king                  1         7698 blake  king           2         7499 allen  blake          3         7900 james  blake          3         7654 martin blake          3         7844 turner blake          3         7521 ward   blake          3         7782 clark  king           2         7934 miller clark          3         7566 jones  king           2         7902 ford   jones          3         empno ename  mgr        level   ---------- ------ ----- ----------         7369 smith  ford           4         7788 scott  jones          3         7876 adams  scott          4    14 rows selected.    sql> 

• w_data fake out test data ..
• connect mgr = prior ename takes each record, , compares mgr previous level employee.
• start mgr null tells oracle start ...
• level depth of heirarchy.