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).
# 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 Documentation, versions (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html), (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)
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…
A simple example of `LEVEL` and `SYS_CONNECT_BY_PATH`:
There is a trick to get any specified number of rows from `dual`: