CONNECT BY Hierarchical Queries
Concept
oracle add tag
Community 
Hierarchical data can be queried in hierarchical order using `START WITH` and `CONNECT BY`.

* `START WITH` specifies the root row(s).

* `CONNECT BY`, followed by a condition, specifies the hierarchical relationship between rows. At least one expression in the condition must use the `PRIOR` operator, which refers to the previous row in the hierarchy (the parent).

![railroad](/image?hash=113f1d96f60cdd6e61b0734df0cf2fd37a94cc39fa8580d71c9b172857ab2bfd)

# Further Details

* `LEVEL` pseudocolumn

  The depth of a row in the hierarchy. 

* `CONNECT_BY_ROOT` operator

  This can be applied to any column to show the value of the same column in the root row rather than the current row.

* `SYS_CONNECT_BY_PATH(column,char)` function

  Returns the path of the column from root to the current row. Each value is separated by `char`.

* `CONNECT_BY_ISCYCLE` pseudocolumn

  `CONNECT BY` will return an error if there is a 'loop', unless followed with the optional `NOCYCLE` parameter. In that case, `CONNECT_BY_ISCYCLE` shows when the cycle has been detected and broken.

# See Also

* [ORACLE-BASE](https://oracle-base.com/articles/misc/hierarchical-queries)
* Oracle Documentation, versions [19](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html), [18](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html), [12.2](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Hierarchical-Queries.html), [12.1](https://docs.oracle.com/database/121/SQLRF/queries003.htm), [11.1](https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm), [10.2](https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm)
Top Answer warning
Jack Douglas 
As the documentation says, ORDER BY or GROUP BY destroy the depth-first hierarchical ordering of the results:

> In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will override the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause…
Answer #2 example
Jack Douglas 
A simple example of `LEVEL` and `SYS_CONNECT_BY_PATH`:

<>https://dbfiddle.uk/?rdbms=oracle_18&fiddle=00add475919b8060bdcb2f41a3351aec
Answer #3 example
Jack Douglas 
There is a trick to get any specified number of rows from `dual`:

<>https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7da53d43d49fd0a612292aa813fc1890
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.