Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Ordering Data with the Hierarchical Query

Description 

The ORDER SIBLINGS BY clause will cause the ordering of the rows while preserving the hierarchy ordering so that the child nodes with the same parent will be stored according to the column list.

Syntax

ORDER SIBLINGS BY col_1 [ASC|DESC] [, col_2 [ASC|DESC] […[, col_n [ASC|DESC]]…]]

Example 1

The following example shows how to output information about seniors and subordinates in a company in the order of birth year.

The result with hierarchical query shows parent and child nodes in a row according to the column list specified in ORDER SIBLINGS BY statement by default. Sibling nodes that share the same parent node have outputted in a specified order.

-- Outputting a parent node and its child nodes, which sibling nodes that share the same parent are sorted in the order of birth year.

SELECT id, mgrid, name, birthyear, level

FROM tree

START WITH mgrid IS NULL

CONNECT BY PRIOR id=mgrid

ORDER SIBLINGS BY birthyear;

 

id        mgrid  name                    birthyear        level

==========================================================================

2         NULL  'Moy'                        1958            1

6            2  'Foster'                     1972            2

7            6  'Brown'                      1981            3

5            2  'Verma'                      1973            2

1         NULL  'Kim'                        1963            1

4            1  'Smith'                      1974            2

3            1  'Jonas'                      1976            2

Example 2

The following example shows how to output information about seniors and subordinates in a company in the order of joining. For the same level, the employee ID numbers are assigned in the order of joining. id indicates employee ID numbers (parent and child nodes) and mgrid indicates the employee ID numbers of their seniors.

-- Outputting siblings in a row

SELECT id, mgrid, name, LEVEL

FROM tree

START WITH mgrid IS NULL

CONNECT BY PRIOR id=mgrid

ORDER SIBLINGS BY id;

 

id  mgrid       name        level

===============================================

1   null        Kim     1

3   1       Jonas       2

4   1       Smith       2

2   null        Moy     1

5   2       Verma       2

6   2       Foster      2

7   6       Brown       3