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:
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 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
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 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, 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 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
Just observed that it'll count all children not just the grandchildren, therefore it is not 100% what you would like.