Finding Descendant Depth in an Adjacency List Table

Question

Question

Example Table

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Given the example table above, I'm thinking in Oracle I should be able to write SQL along the lines of "SELECT ... CONNECT BY" to find the level of a given id. For example, "MP3 Players" has a LEVEL of 3.

Example borrowed from Managing Hierarchical Data in MySQL

Solution

SELECT MAX(level) "LEVEL"
FROM TEST_TABLE
START WITH category_id = 7 -- MP3 Players category_id
CONNECT BY category_id = PRIOR parent
ORDER BY LEVEL DESC

Answer 1

You can use level pseudocolumn to get the "depth" of the current row. Consider the following statement:

SELECT category_id, name, level,
 PRIOR name as parent_category
FROM test_table
START WITH parent is null
CONNECT BY PRIOR category_id = parent