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

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

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

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.