AntDB-Oracle Compatibility Developer’s Manual P3–24
Published: 2023-09-05 03:57:16

##### CONNECT BY clause

When executing a hierarchical query, use the CONNECT BY clause to determine the parent-child relationship between rows. The usage method of CONNECT BYclause is as follows:

CONNECT BY { PRIOR parent\_expr = child\_expr \|

child\_expr = PRIOR parent\_expr }

parent\_expr is an expression that is computed on the candidate parent record. If the result of the expression parent\_expr = child\_expr is “true” for the record returned by the FROM clause, then the record is treated as a child node record on the parent record.

The following optional clause can be specified in an association with the CONNECT BY clause.

START WITH start\_expression

The record returned by the FROM clause with the value “true” on start\_expression becomes the root of the hierarchy.

ORDER SIBLINGS BY expression \[ ASC \| DESC ] \[, …]

The rows of the same level node in the hierarchy are sorted by the expression in the result set.

##### ORDER BY clause

ORDER BY clause is optional and its usage method is as follow:

ORDER BY expression \[ ASC \| DESC ] \[, …]

The expression can be the name and ordinal number of the output column (SELECT list item), or it can be any expression formed from the input column values.

We can use the ORDER BY clause to sort the result set according to the specified expression. The exact order of comparison starts with the leftmost expression, and if two records are equal, then they are compared based on the next expression. If the records are equal according to all the specified expressions, then they are returned in the order they were applied.

The ordinal number is the position of the result column (in left-to-right order). This feature can be used to define the order of sorting based on the absence of a unique name column. Of course, this is not absolutely necessary, as the AS clause can be used to assign a name to the column.

We can use any expression in the ORDER BY clause that contains columns that do not appear in the list of SELECT commands. Thus, the following statement is valid.

```
SELECT ename FROM emp ORDER BY empno; 
```
The limitation of this feature is that an ORDER BY clause applied to a UNION, INTERSECT, or MINUS result set may output only the name or number of the column, not the expression.

If the ORDER BY expression is a simple name that matches the names of all result and input columns, ORDER BY will interpret it as the name of the result column, which is the opposite of the choice of the GROUP BY clause in the same case. This inconsistency is for compatibility with the SQL standard.

We can also add the keywords ASC (ascending) or DESC (descending) to the end of the expression in the ORDER BY clause. If these keywords are not present, then ASC is used as the default keyword in the default case.

The null sorting level is higher than any other value, in other words, in ascending sorting, the null value is in is the last position, and in descending sorting, the null value is in the beginning position.

String data is sorted according to the locally specified check order established at database cluster initialization.