Re: Return the level and count of a closure table.
I've added a level column to my table.
+-------------+---------------+-------+
| ancestor_id | descendant_id | level |
+-------------+---------------+-------+
| 1 | 2 | 1 |
+-------------+---------------+-------+
| 1 | 3 | 1 |
+-------------+---------------+-------+
| 1 | 4 | 1 |
+-------------+---------------+-------+
| 1 | 5 | 1 |
+-------------+---------------+-------+
| 1 | 6 | 1 |
+-------------+---------------+-------+
| 1 | 7 | 1 |
+-------------+---------------+-------+
| 2 | 3 | 2 |
+-------------+---------------+-------+
| 2 | 5 | 2 |
+-------------+---------------+-------+
| 2 | 6 | 2 |
+-------------+---------------+-------+
| 3 | 5 | 3 |
+-------------+---------------+-------+
| 3 | 6 | 3 |
+-------------+---------------+-------+
| 4 | 7 | 2 |
+-------------+---------------+-------+
I'm not really sure on what to do next. I've never dealt with data like this before, so this is very foreign to me.
What should the query look like? What do I do with this new column?
I tried something like this:
SELECT ancestor_id, descendant_id, MAX(level) AS level
FROM my_tree
GROUP BY descendant_id
This creates a nice table like this:
+-------------+---------------+-------+
| ancestor_id | descendant_id | level |
+-------------+---------------+-------+
| 1 | 2 | 1 |
+-------------+---------------+-------+
| 1 | 4 | 1 |
+-------------+---------------+-------+
| 1 | 3 | 2 |
+-------------+---------------+-------+
| 1 | 5 | 3 |
+-------------+---------------+-------+
| 1 | 6 | 3 |
+-------------+---------------+-------+
| 1 | 7 | 2 |
+-------------+---------------+-------+
Which is nice because it give me a table where each row is a node. But how can I group and count the level column if I'm already grouping the descendant_id column? If I could GROUP BY level on this result set and count it, I think it would be what I want.
Edited 1 time(s). Last edit at 05/07/2009 10:16AM by Sam Holmes.