We help IT Professionals succeed at work.

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

teera
teera asked
on
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

etc  

Comment
Watch Question

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

Author

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


       rank
c        1
b        2
a        3



Author

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


       rank
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

       rank
c        1
b        2
a        3
f        1
CERTIFIED EXPERT
Commented:
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)
	UPDATE T 
	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.