How to get the id with the biggest number of children

Question

So I have this table :

+----+-----------+----------+
| id | id_parent | id_child |
+----+-----------+----------+
|  1 |        10 |        9 |
|  2 |        11 |        9 |
|  3 |        12 |       11 |
|  4 |        13 |       11 |
|  5 |        14 |       13 |
+----+-----------+----------+

And I'd like to select the parent with the most children. Obviously if 9 is a child of 11 and 11 is a child of 13, then 9 is a child of 13. here if I use :

SELECT id_parent,COUNT(id_child) AS count
from dependency
GROUP BY id_parent ORDER BY count DESC;

It would give 1 for each parent. Is there any way to do this without adding more records to the database. (That means without adding 9 child of 13...etc.) (Modification to the table schema could also be a solution).

Is the height of the tree fixed, or could a parent have an arbitrary number of children? — Tim Biegeleisen, Feb 24 '16 at 02:29
It's completely random. And numbers can really grow to become huge, so I want it to be the most optimized possible — Anthony N., Feb 24 '16 at 02:31

score 0 · Answer 1 · edited May 23 '17 at 12:15

For what you are trying to achieve you will need to use recursion.

Have a look at an example here: How to do the Recursive SELECT query in MySQL?

In short, you identify the top parents, the ones that don't have a parent. You would then recursively find all the children and their children's children etc. You can keep track of the count in a temp table. You can then query the temporary table to find the parent with the most children.

Do you count only the direct children of the parent? or do you need all the children (including the nested ones)?

How to get the id with the biggest number of children

1 Answers1