select name of a person with the most grandchildren

I have a simple table with persons, but there is a additional

field witch holds information (person id) who is a father/mother

of that person, so the 2 dimensional table can hold a familly tree

the table is

id first_name  last_name salary spouse_id father_id mother_id sex
100 Steven King 26400 101 (null) (null) m  
101 Neena Kochhar 18700 100 (null) (null) f  
102 Lex De Haan 18700 106 100 101 m  
103 Alexander Hunold 9900 (null) 100 101 m  
104 Bruce Ernst 6600 (null) 102 106 m  
105 David Austin 5280 (null) 102 106 m  
10开发者_StackOverflow中文版6 Valli Pataballa 5280 102 (null) (null) f  
107 Diana Lorentz 4620 (null) (null) (null) f  
108 Nancy Greenberg 13200 109 (null) (null) f  
109 Daniel Faviet 9900 108 115 116 m  
110 John Chen 9020 (null) 109 108 m  
111 Ismael Sciarra 8470 (null) 109 108 m  
112 Jose Manuel Urman 8580 (null) 109 108 m  
113 Luis Popp 7590 (null) 109 108 m  
114 Den Raphaely 12100 (null) 109 108 m  
115 Alexander Khoo 3410 116 (null) (null) m  
116 Shelli Baida 3190 115 (null) (null) f  

The task is to select person name which has biggest number of grandchildren

All I managed to do is:

select 
e1.first_name, e1.last_name
--,max (e3.first_name)
,count(e3.first_name) grandchilds
from empnew e1
inner join
empnew e2
on (e1.id = e2.father_id)
inner join
empnew e3
on (e2.id = e3.father_id)
group by e1.first_name, e1.last_name

and the result is

first_name last_name grandchilds
Steven King 2
Alexander Khoo 5

please help :) ps: I would like to get RDBMS independent answer if it is possible

---

I think I did it, please take a look at my solution and comment it

SELECT 
e1.first_name
, e1.last_name
, count(e3.first_name) AS grandchilds
FROM empnew e1
INNER JOIN
empnew e2
ON (e1.id = e2.father_id)
INNER JOIN
empnew e3
ON (e2.id = e3.father_id)
GROUP BY e1.first_name, e1.last_name
HAVING COUNT(e3.first_name)
=
(SELECT MAX (grandchilds) FROM
(
SELECT 
e1.first_name
, COUNT(e3.first_name) AS grandchilds
FROM empnew e1
INNER JOIN
empnew e2
ON (e1.id = e2.father_id)
INNER JOIN
empnew e3
ON (e2.id = e3.father_id)
GROUP BY e1.first_name
) table_1);

edit: I fixed it up as 'onedaywhen' said

---

The following is an ANSI solution except for the strpos function (which is PostgreSQL specific). But it shouldn't be hard to find the correct function that finds a substring in another string.

with recursive person_tree as (
   select id, first_name, last_name, cast(id as varchar)||'/' as id_path, id as root_id
   from persons
   where father_id is null

   union all

   select c.id, c.first_name, c.last_name, id_path || cast(c.id as varchar)||'/', null
   from persons c 
     join person_tree p on c.father_id = p.id
),
group_flags as (
  select id_path, 
         id,
         first_name,
         last_name,
         substring(id_path, 0, strpos(id_path, '/')) as root_id
  from person_tree
)
select root_id, count(*) 
from group_flags
group by root_id
having count(*) = (select max(children_count)
                   from (select root_id,
                                count(*) as children_count
                         from group_flags
                         group by root_id
                   ) t) 

I tested this with PostgreSQL, but it should also work on Firebird, SQL Server, DB2, Oracle 11gR2 and Teradata. Not all of them accept the (according to the standard) mandatory keyword recursive, so you might need to remove that depending on the target DBMS.

SQL Server breaks the standard by not using || for string concatenation. You must use + instead.

Edit:

Just noticed that it will count all children not only the grandchildren, so it's not 100% what you want.