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

To better understand this, let us consider the following scenario:
We have one parent node (named "R") which has two children, C1 and C2. Each of these children has three children of it's own, C1_1, C1_2, and C1_3 and C2_1 and C2_2, C2_3. If we don't specify ORDER BY, the rows are obtained in depth-first order but without further ordering. Let's say we want them sorted. Assuming that we sort the rows with a simple ORDER BY we obtain the following order: 

ROWS

COLUMN VALUES

LEVEL

R(parent node)

A

Level 1

C1

B

Level 2

C1_1

G

Level 3

C1_2

F

Level 3

C1_3

H

Level 3

C2

D

Level 2

C2_1

C

Level 3

C2_2

E

Level 3

C2_3

I

Level 3

We have correctly ordered them but the hierarchical ordering is broken. The ORDER SIBLINGS BY clause will cause the order of the row to be as displayed below.

ROWS

COLUMN VALUES

LEVEL

R

A

Level 1

C1

B

Level 2

C2_1

C

Level 3

C2

D

Level 2

C2_2

E

Level 3

C1_2

F

Level 3

C1_1

G

Level 3

C1_3

H

Level 3

C2_3

I

Level 3

Basically, the tree is displayed vertically with the child nodes of a parent sorted according to the column list found in the clause. All siblings are in successive rows and sorted according to the desired criteria.

Example 2

The following is an example of sorting rows which share a parent.

--Displaying siblings in successive rows

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