| Bug #2341 | CONNECT BY PRIOR | ||
|---|---|---|---|
| Submitted: | 9 Jan 2004 18:00 | Modified: | 7 Jan 2020 9:33 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | Next One | OS: | Any (any) |
| Assigned to: | CPU Architecture: | Any | |
[9 Jan 2004 18:00]
[ name withheld ]
[4 Oct 2005 17:19]
Hartmut Holzgraefe
see also worlog entry 576
[12 Jan 2006 5:27]
Rene Pitayataratorn
Hi, In support of your feature request I would like to mention that CONNECT BY PRIOR / Oracle style or SELECT RECURSIVE / DB2 style recursive query support has been promised as a feature since 4.1 (s. also http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/TODO_future.html). Numerous authors have contributed theory and work arounds (incl. Celkos. http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=697912) ranging from Nested Sets (http://www.klempert.de/php/nested_sets/) to PHP classes. However, the performance aspect of the approach - nothing can be better than including that feature than a SQL syntax support with a clause limiting the recursion depth incl. as standard feature of the distribution. All major RDBMS have support for this meanwhile. Cheers, Rene
[1 Nov 2009 13:22]
bill barsch
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.
[20 Dec 2016 11:52]
yvs ppt
Is it possible to get informations about roadmap of the development of this old request ? As "connect by" is not standard syntax defined by SQL:1999/SQL:2003, I think the statement "WITH RECURSIVE" could be used.
[7 Jan 2020 9:33]
Norvald Ryeng
Posted by developer: Common table expressions (CTEs), including recursive CTEs, were implemented in MySQL 8.0.1 and covers the same use case (https://dev.mysql.com/doc/refman/8.0/en/with.html). We're not going to implement CONNECT BY PRIOR.
