MySQL Forums
Forum List  »  Newbie

Re: Return the level and count of a closure table.
Posted by: Sam Holmes
Date: May 07, 2009 09:45AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.