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.