MySQL Forums

the solution to order by depth on recursive querys with hierarchy data
is stored function like this:
obs: it´s not recursive but do the work!
***********************************************************************
DELIMITER $$

CREATE FUNCTION `depth_of_node`(v_nodeID integer) RETURNS INT
READS SQL DATA
BEGIN

DECLARE v_depth int;
DECLARE v_parentNodeID int;
DECLARE v_currentNode int;
declare v_The_End BOOL default FALSE;
declare continue handler for not found set v_The_End := TRUE;

SET v_currentNode = v_nodeID;
SET v_depth = -1;

WHILE (v_The_End = false) DO

SELECT table_name.PARENTNODE into v_parentNode
FROM table_name
WHERE table_name.nodeID = v_currentNode;

SET v_currentNode = v_parentNode;
SET v_depth = v_depth + 1;

end while;

return v_depth;

END $$
DELIMITER ;
**************************************************************************

use it as:

SELECT table_name.NodeName,
depth_of_node(table_name.nodeID) as depth
FROM table_name
LEFT JOIN table_name t2 ON (table_name.parentID = t2.nodeID)
WHERE table_name.COL_NAME = "xxxxxx"
AND table_name.COL_NAME2 = "xxxxxx"
ORDER BY depth_of_node(table_name.nodeID)

It will result some like this:
**************************************************************
Node Name| Depth
*************|***********************************************
A | 1
B | 2
C | 3
D | 4
*************************************************************

hope it help.