We help IT Professionals succeed at work.

number of reference  mysql  write sql in my sql to get the rank

teera asked
the table below show  
 nae is ahead of  michale , cheery, victoria
susan is ahead of nae
david is ahead of susan

id      robert      headid
1      david      
2      nae              4
3      michale      2
4      susan      1
5      cheery      2
6      victoria      2
7      michele      2
8      nicole      3
9      taylor      2
10      rossom      2

how can I write the code in sql to show

name   rank
david    3
susan   2
nae       1


Watch Question

Can you explain more about on what basis you would like to generate the Rank.


a is a head of b
b is a head of c
c is the head of d

c        1
b        2
a        3


a is a head of b   b is a head of c  c is the head of d

c        1
b        2
a        3

I have colum name and Hid (hierarchy id)
Hid related to id

id   name   Hid
1   a        
2   b        1
3   c        2
4   d        3
5   f        
6   g        5

How can I use sql statement to find this table

c        1
b        2
a        3
f        1
looks to me that you're looking for the depth in a hierarchy. In mySQL you can try the below:

1) You need to have a new column, which in this case I named "level". If you don't have it already, then create a new table (it could be a temp table if you prefer) and copy your data over.

create table htable
	id int,
	name varchar(20),
	Hid int,
	level int

insert htable
select * from yourtable

Open in new window

2) Update all the roots nodes, which in this case are all that have Hid = null. (i.e. a and f)

update htable set level = 0 where hid is null

3) Run the following to populate all remaining levels

while exists(select 1 from htable where level is null)
	SET T.level = P.level + 1
	FROM Htable AS T 
	INNER JOIN hTable AS P ON (T.Hid=P.ID) 
	WHERE P.Level>=0 
	AND T.Level Is Null

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.