Hi I've got a simple table with persons, but there's a additional
area witch holds information (person id) who's a parentOrmom
of this person, therefore the 2 dimensional table holds 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  
106 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 job would be to choose person title that has greatest quantity of grandchildren

All I handled to complete is:

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 it makes sense

first_name last_name grandchilds
Steven King 2
Alexander Khoo 5

help :) ps: I must get RDBMS independent answer if it's possible

I believe Used to do it, please have a look inside my solution and comment it

, e1.last_name
, count(e3.first_name) AS grandchilds
FROM empnew e1
empnew e2
ON (e1.id = e2.father_id)
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
, COUNT(e3.first_name) AS grandchilds
FROM empnew e1
empnew e2
ON (e1.id = e2.father_id)
empnew e3
ON (e2.id = e3.father_id)
GROUP BY e1.first_name
) table_1);

edit: I fixed up as 'onedaywhen' stated

This is an ANSI solution aside from the strpos function (that is PostgreSQL specific). However it should not be difficult to find the right 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, 
         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 examined this with PostgreSQL, however it also needs to focus on Firebird, SQL Server, DB2, Oracle 11gR2 and Teradata. Not every one of them accept the (based on the standard) mandatory keyword recursive, so you may have to remove that with respect to the target DBMS.

SQL Server breaks the conventional by not using || for string concatenation. You have to use + rather.


Just observed that it'll count all children not just the grandchildren, therefore it is not 100% what you would like.