## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# Complex Hierarchy Operations

In this section, we discuss how we can use Oracle SQL’s hierarchical extensions to perform complex hierarchical queries.

## Finding the Number of Levels

Previously we showed how the LEVEL pseudocolumn generates a level number for each record when we use the START WITH...CONNECT BY clause. We can use the following query to determine the number of levels in the hierarchy by counting the number of distinct level numbers returned by the LEVEL pseudocolumn:

```               `SELECT COUNT(DISTINCT LEVEL) `
`FROM EMPLOYEE`
`START WITH MANAGER_EMP_ID IS NULL`
`CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;`

COUNT(DISTINCTLEVEL)
--------------------
4```

To determine the number of employees at each level, group the results by LEVEL and count the number of employees in each distinct group. For example:

```               `SELECT LEVEL, COUNT(EMP_ID) `
`FROM EMPLOYEE`
`START WITH MANAGER_EMP_ID IS NULL`
`CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID`
`GROUP BY LEVEL;`

LEVEL COUNT(EMP_ID)
--------- -------------
1             1
2             3
3             8
4             2```

## Listing Records in Hierarchical Order

One of the very common programming challenges SQL programmers face is to list records in a hierarchy in their proper hierarchical order. For example, we might wish to list employees with their subordinates underneath them, as is in the following query:

```               `SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || LNAME "EMPLOYEE",`
`       EMP_ID, MANAGER_EMP_ID`
`FROM EMPLOYEE`
`START WITH MANAGER_EMP_ID IS NULL`
`CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;` LEVEL Employee EMP_ID MANAGER_EMP_ID --------- ...```

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required