Hierarchical query - limiting number of elements per level












2















I am working with an Oracle DBMS and I have a question regarding hierarchical queries.



I am creating an org list for my department at work with the use of a hierarchical SQL query. There are three levels to the org structure which are, in order, department manager, section manager, and team member.



The query I have so far is: 



SELECT level, employee_number, name, manager, department, phone

FROM employee_table

START WITH manager is null

CONNECT BY PRIOR employee_number = manager;


This query produces a hierarchical list in which the department manager is at level one, each of the section managers is at level two, and the team members are at level three. Overall, there is only one department manager, three section managers, and 30 team members.



The problem: My requirement is to limit the number of employees at each level in the list to a maximum of three employees. This is no problem for the first two levels since there is only one department manager and three section managers (there will never be more than three section managers), but I currently have 30 team members at level three (each section manager manages 10 team members). My goal is to have three team members at level 3, three team members at level 4, three team members at level 5, etc. The order of the team members doesn't matter, so it doesn't matter which team members are at level 3, which team members are at level 4, etc.



I would prefer to avoid setting the manager of a team member be the employee_number of another team member just to achieve this goal. I could just create another column in the employee_table called something like "org_list_parent" and indicate that the "org_list_parent" of one team member is the employee_number of another team member, but I would prefer to avoid doing that as well, if possible.



Does anyone have any thoughts on this problem?



Thank you in advance.



UPDATE:



Using mathguy's query, I was able to get the closest output to what I was trying to achieve. However, there is something I would like to tweak about the output, if possible. I am using this list to build an Org Chart in Oracle Apex, and with mathguy's table and query, I get the following output:



enter image description here



This is very close to the visual I am trying to produce. The reason behind the "3 team members per level" is to try to prevent the chart from being excessively large horizontally. However, if you look to the left, for example, Employees 1104, 1105, and 1106 are underneath 1103 and employee 1107 is underneath 1106. It would be best if employee 1104 were underneath 1101, employee 1105 underneath 1102, employee 1106 underneath 1103, and employee 1107 underneath 1104. Is there any way to edit the query so that it produces that result visually?



Update 2:



alexgibbs requested feedback on the two different queries he suggested as solutions for my problem. The following is his first query:



SELECT CASE WHEN LEVEL < 3

          THEN LEVEL

        ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER, MANAGER

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY 2 ASC, 1 ASC;


And the following is the output for this query as a list in Oracle Apex:



enter image description here



The following is his second query:



SELECT

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER 

ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


And the following is the output for this query in Oracle Apex:



enter image description here






sql oracle oracle11g hierarchy







share|improve this question

























  • A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

    – mathguy
    Nov 21 '18 at 23:47











  • Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

    – Katherine Reed
    Nov 21 '18 at 23:56











  • Thanks Katherine. What would you want to happen if a fourth section manager were added?

    – alexgibbs
    Nov 22 '18 at 0:07











  • That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

    – Katherine Reed
    Nov 22 '18 at 0:17











  • Ok thanks Katherine. I'll proceed along those lines.

    – alexgibbs
    Nov 22 '18 at 0:18
















2















I am working with an Oracle DBMS and I have a question regarding hierarchical queries.



I am creating an org list for my department at work with the use of a hierarchical SQL query. There are three levels to the org structure which are, in order, department manager, section manager, and team member.



The query I have so far is: 



SELECT level, employee_number, name, manager, department, phone

FROM employee_table

START WITH manager is null

CONNECT BY PRIOR employee_number = manager;


This query produces a hierarchical list in which the department manager is at level one, each of the section managers is at level two, and the team members are at level three. Overall, there is only one department manager, three section managers, and 30 team members.



The problem: My requirement is to limit the number of employees at each level in the list to a maximum of three employees. This is no problem for the first two levels since there is only one department manager and three section managers (there will never be more than three section managers), but I currently have 30 team members at level three (each section manager manages 10 team members). My goal is to have three team members at level 3, three team members at level 4, three team members at level 5, etc. The order of the team members doesn't matter, so it doesn't matter which team members are at level 3, which team members are at level 4, etc.



I would prefer to avoid setting the manager of a team member be the employee_number of another team member just to achieve this goal. I could just create another column in the employee_table called something like "org_list_parent" and indicate that the "org_list_parent" of one team member is the employee_number of another team member, but I would prefer to avoid doing that as well, if possible.



Does anyone have any thoughts on this problem?



Thank you in advance.



UPDATE:



Using mathguy's query, I was able to get the closest output to what I was trying to achieve. However, there is something I would like to tweak about the output, if possible. I am using this list to build an Org Chart in Oracle Apex, and with mathguy's table and query, I get the following output:



enter image description here



This is very close to the visual I am trying to produce. The reason behind the "3 team members per level" is to try to prevent the chart from being excessively large horizontally. However, if you look to the left, for example, Employees 1104, 1105, and 1106 are underneath 1103 and employee 1107 is underneath 1106. It would be best if employee 1104 were underneath 1101, employee 1105 underneath 1102, employee 1106 underneath 1103, and employee 1107 underneath 1104. Is there any way to edit the query so that it produces that result visually?



Update 2:



alexgibbs requested feedback on the two different queries he suggested as solutions for my problem. The following is his first query:



SELECT CASE WHEN LEVEL < 3

          THEN LEVEL

        ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER, MANAGER

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY 2 ASC, 1 ASC;


And the following is the output for this query as a list in Oracle Apex:



enter image description here



The following is his second query:



SELECT

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER 

ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


And the following is the output for this query in Oracle Apex:



enter image description here






sql oracle oracle11g hierarchy







share|improve this question

























  • A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

    – mathguy
    Nov 21 '18 at 23:47











  • Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

    – Katherine Reed
    Nov 21 '18 at 23:56











  • Thanks Katherine. What would you want to happen if a fourth section manager were added?

    – alexgibbs
    Nov 22 '18 at 0:07











  • That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

    – Katherine Reed
    Nov 22 '18 at 0:17











  • Ok thanks Katherine. I'll proceed along those lines.

    – alexgibbs
    Nov 22 '18 at 0:18














2












2








2








I am working with an Oracle DBMS and I have a question regarding hierarchical queries.



I am creating an org list for my department at work with the use of a hierarchical SQL query. There are three levels to the org structure which are, in order, department manager, section manager, and team member.



The query I have so far is: 



SELECT level, employee_number, name, manager, department, phone

FROM employee_table

START WITH manager is null

CONNECT BY PRIOR employee_number = manager;


This query produces a hierarchical list in which the department manager is at level one, each of the section managers is at level two, and the team members are at level three. Overall, there is only one department manager, three section managers, and 30 team members.



The problem: My requirement is to limit the number of employees at each level in the list to a maximum of three employees. This is no problem for the first two levels since there is only one department manager and three section managers (there will never be more than three section managers), but I currently have 30 team members at level three (each section manager manages 10 team members). My goal is to have three team members at level 3, three team members at level 4, three team members at level 5, etc. The order of the team members doesn't matter, so it doesn't matter which team members are at level 3, which team members are at level 4, etc.



I would prefer to avoid setting the manager of a team member be the employee_number of another team member just to achieve this goal. I could just create another column in the employee_table called something like "org_list_parent" and indicate that the "org_list_parent" of one team member is the employee_number of another team member, but I would prefer to avoid doing that as well, if possible.



Does anyone have any thoughts on this problem?



Thank you in advance.



UPDATE:



Using mathguy's query, I was able to get the closest output to what I was trying to achieve. However, there is something I would like to tweak about the output, if possible. I am using this list to build an Org Chart in Oracle Apex, and with mathguy's table and query, I get the following output:



enter image description here



This is very close to the visual I am trying to produce. The reason behind the "3 team members per level" is to try to prevent the chart from being excessively large horizontally. However, if you look to the left, for example, Employees 1104, 1105, and 1106 are underneath 1103 and employee 1107 is underneath 1106. It would be best if employee 1104 were underneath 1101, employee 1105 underneath 1102, employee 1106 underneath 1103, and employee 1107 underneath 1104. Is there any way to edit the query so that it produces that result visually?



Update 2:



alexgibbs requested feedback on the two different queries he suggested as solutions for my problem. The following is his first query:



SELECT CASE WHEN LEVEL < 3

          THEN LEVEL

        ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER, MANAGER

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY 2 ASC, 1 ASC;


And the following is the output for this query as a list in Oracle Apex:



enter image description here



The following is his second query:



SELECT

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER 

ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


And the following is the output for this query in Oracle Apex:



enter image description here






sql oracle oracle11g hierarchy







share|improve this question
















I am working with an Oracle DBMS and I have a question regarding hierarchical queries.



I am creating an org list for my department at work with the use of a hierarchical SQL query. There are three levels to the org structure which are, in order, department manager, section manager, and team member.



The query I have so far is: 



SELECT level, employee_number, name, manager, department, phone

FROM employee_table

START WITH manager is null

CONNECT BY PRIOR employee_number = manager;


This query produces a hierarchical list in which the department manager is at level one, each of the section managers is at level two, and the team members are at level three. Overall, there is only one department manager, three section managers, and 30 team members.



The problem: My requirement is to limit the number of employees at each level in the list to a maximum of three employees. This is no problem for the first two levels since there is only one department manager and three section managers (there will never be more than three section managers), but I currently have 30 team members at level three (each section manager manages 10 team members). My goal is to have three team members at level 3, three team members at level 4, three team members at level 5, etc. The order of the team members doesn't matter, so it doesn't matter which team members are at level 3, which team members are at level 4, etc.



I would prefer to avoid setting the manager of a team member be the employee_number of another team member just to achieve this goal. I could just create another column in the employee_table called something like "org_list_parent" and indicate that the "org_list_parent" of one team member is the employee_number of another team member, but I would prefer to avoid doing that as well, if possible.



Does anyone have any thoughts on this problem?



Thank you in advance.



UPDATE:



Using mathguy's query, I was able to get the closest output to what I was trying to achieve. However, there is something I would like to tweak about the output, if possible. I am using this list to build an Org Chart in Oracle Apex, and with mathguy's table and query, I get the following output:



enter image description here



This is very close to the visual I am trying to produce. The reason behind the "3 team members per level" is to try to prevent the chart from being excessively large horizontally. However, if you look to the left, for example, Employees 1104, 1105, and 1106 are underneath 1103 and employee 1107 is underneath 1106. It would be best if employee 1104 were underneath 1101, employee 1105 underneath 1102, employee 1106 underneath 1103, and employee 1107 underneath 1104. Is there any way to edit the query so that it produces that result visually?



Update 2:



alexgibbs requested feedback on the two different queries he suggested as solutions for my problem. The following is his first query:



SELECT CASE WHEN LEVEL < 3

          THEN LEVEL

        ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER, MANAGER

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY 2 ASC, 1 ASC;


And the following is the output for this query as a list in Oracle Apex:



enter image description here



The following is his second query:



SELECT

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY 

EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

 THEN LEVEL

 ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER 

ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


And the following is the output for this query in Oracle Apex:



enter image description here






sql oracle oracle11g hierarchy




sql oracle oracle11g hierarchy






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 24 '18 at 22:48







Katherine Reed

















asked Nov 21 '18 at 23:39









Katherine ReedKatherine Reed

12910




12910













  • A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

    – mathguy
    Nov 21 '18 at 23:47











  • Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

    – Katherine Reed
    Nov 21 '18 at 23:56











  • Thanks Katherine. What would you want to happen if a fourth section manager were added?

    – alexgibbs
    Nov 22 '18 at 0:07











  • That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

    – Katherine Reed
    Nov 22 '18 at 0:17











  • Ok thanks Katherine. I'll proceed along those lines.

    – alexgibbs
    Nov 22 '18 at 0:18



















  • A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

    – mathguy
    Nov 21 '18 at 23:47











  • Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

    – Katherine Reed
    Nov 21 '18 at 23:56











  • Thanks Katherine. What would you want to happen if a fourth section manager were added?

    – alexgibbs
    Nov 22 '18 at 0:07











  • That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

    – Katherine Reed
    Nov 22 '18 at 0:17











  • Ok thanks Katherine. I'll proceed along those lines.

    – alexgibbs
    Nov 22 '18 at 0:18

















A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

– mathguy
Nov 21 '18 at 23:47





A "hierarchical query", in the absence of LEVEL and other such calculated things in the SELECT list, is nothing but an ordering of rows, depth first. Do you care about that order? Other than that, in your SELECT list you just have LEVEL - is that the only thing you need to change? Also: When you say only three employees per level, do you mean over ALL rows, or only "three employees at the same level FOR EACH MANAGER" (so a total of nine employees at each level overall)?

– mathguy
Nov 21 '18 at 23:47













Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

– Katherine Reed
Nov 21 '18 at 23:56





Three employees at the same level for each manager, so a total of nine employees at each level overall. I don't fully understand your first two questions, but essentially what I am looking for is the single department manager at level 1, the 3 section managers at level 2 (the order of the section managers does not matter), 9 team members at level 3 (3 for each section manager), 9 team members at level 4, etc. It doesn't matter which team members are at which level (3, 4, etc), but the parent of the team members in level 3 need to be their respective section manager in level 2.

– Katherine Reed
Nov 21 '18 at 23:56













Thanks Katherine. What would you want to happen if a fourth section manager were added?

– alexgibbs
Nov 22 '18 at 0:07





Thanks Katherine. What would you want to happen if a fourth section manager were added?

– alexgibbs
Nov 22 '18 at 0:07













That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

– Katherine Reed
Nov 22 '18 at 0:17





That's a good question, @alexgibbs, and while I do not anticipate it ever happening, I suppose it is possible. In that case I would prefer that the fourth section manager to be at level two with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.

– Katherine Reed
Nov 22 '18 at 0:17













Ok thanks Katherine. I'll proceed along those lines.

– alexgibbs
Nov 22 '18 at 0:18





Ok thanks Katherine. I'll proceed along those lines.

– alexgibbs
Nov 22 '18 at 0:18












2 Answers
2






active

oldest

votes


















2














Here is how I would do this.



First the test data (I included enough employees to illustrate the point, but not quite 10 employees for each mid-level manager). I left out the phone number as irrelevant to the problem at hand.



create table employee_table(employee_number, name, manager, department) as

    select 1001, 'Big Boss', null, 100 from dual union all

    select 1100, 'Beth Mgr', 1001, 100 from dual union all

    select 1101, 'Jim'     , 1100, 100 from dual union all

    select 1102, 'Jackie'  , 1100, 100 from dual union all

    select 1103, 'Helen'   , 1100, 100 from dual union all

    select 1104, 'Tom'     , 1100, 100 from dual union all

    select 1105, 'Vance'   , 1100, 100 from dual union all

    select 1106, 'Rosa'    , 1100, 100 from dual union all

    select 1107, 'Chuck'   , 1100, 100 from dual union all

    select 1200, 'Duck Mgr', 1001, 200 from dual union all

    select 1201, 'Danny'   , 1200, 200 from dual union all

    select 1202, 'Henry'   , 1200, 200 from dual union all

    select 1203, 'Mac'     , 1200, 200 from dual union all

    select 1204, 'Hassan'  , 1200, 200 from dual union all

    select 1205, 'Ann'     , 1200, 200 from dual union all

    select 1300, 'Adam Mgr', 1001, 300 from dual union all

    select 1301, 'Wendy'   , 1300, 300 from dual

;


Then the query. I want the output to follow the "hierarchical order" (as it would be if we didn't have to mess with the levels). For that, I run the hierarchical query first, I capture ROWNUM for ordering of the final results, and I modify the level in the outer query. Note that I use LVL as column name; LEVEL is a reserved word, so it should not be used as column name.



select   case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,

         employee_number, name, manager, department

from     (

          select     level as lvl, employee_number, name, manager, department,

                     rownum as ord, 

                     row_number() over 

                        (partition by manager order by employee_number) as rn

          from       employee_table

          start with manager is null

          connect by prior employee_number = manager

         )

order by ord

;


OUTPUT:



       LVL EMPLOYEE_NUMBER NAME        MANAGER DEPARTMENT

---------- --------------- -------- ---------- ----------

         1            1001 Big Boss                   100

         2            1100 Beth Mgr       1001        100

         3            1101 Jim            1100        100

         3            1102 Jackie         1100        100

         3            1103 Helen          1100        100

         4            1104 Tom            1100        100

         4            1105 Vance          1100        100

         4            1106 Rosa           1100        100

         5            1107 Chuck          1100        100

         2            1200 Duck Mgr       1001        200

         3            1201 Danny          1200        200

         3            1202 Henry          1200        200

         3            1203 Mac            1200        200

         4            1204 Hassan         1200        200

         4            1205 Ann            1200        200

         2            1300 Adam Mgr       1001        300

         3            1301 Wendy          1300        300





share|improve this answer
























  • You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:39











  • Yep, no problem - adapt to your needs and come back if you have follow-up questions.

    – mathguy
    Nov 22 '18 at 0:49











  • please look at my update in the original question :)

    – Katherine Reed
    Nov 22 '18 at 1:50





















0














Note: This has been edited significantly in response to an update in the question, and is uncertain/not-tested with apex for compatability



The below solution would just add section managers beyond the third at level 2. But for level 3+, if it does not matter how additional levels are assigned beyond the section managers, one can just assign them based on employee_id. With the update that each member of a group-of-three should have their pseudo-next-level nested directly below, here is an example that assigns pseudo-levels and sub-levels in groups of three, based on employee id, within a department.



Below is an example data set and query.



CREATE TABLE EMPLOYEE_TABLE (

  EMPLOYEE_NUMBER NUMBER,

  MANAGER NUMBER DEFAULT NULL,

  NAME CHARACTER VARYING(64 BYTE),

  DEPARTMENT CHARACTER VARYING(64 BYTE),

  PHONE CHARACTER VARYING(64 BYTE)

);





--Dept Manager

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (10,NULL, 1);

--Section Managers

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (200,10, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (300,10, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (400,10, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (500,10, 5);

-- Section Employees

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2010,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2020,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2030,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2040,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2050,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2060,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2070,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2080,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2090,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2100,200, 2);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3010,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3020,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3030,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3040,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3050,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3060,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3070,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3080,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3090,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3100,300, 3);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4010,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4020,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4030,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4040,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4050,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4060,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4070,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4080,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4090,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4100,400, 4);





INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5010,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5020,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5030,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5040,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5050,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5060,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5070,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5080,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5090,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5100,500, 5);

COMMIT;


Edited
Query: 



SELECT

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


Result:



  ADJUSTED_LEVEL   EMPLOYEE_NUMBER   MANAGER DEPARTMENT

               1                10           1

               2               200        10 2

               3              2010       200 2

               4              2040       200 2

               5              2070       200 2

               6              2100       200 2

               3              2020       200 2

               4              2050       200 2

               5              2080       200 2

               3              2030       200 2

               4              2060       200 2

               5              2090       200 2

               2               300        10 3

               3              3010       300 3

               4              3040       300 3

               5              3070       300 3

               6              3100       300 3

               3              3020       300 3

               4              3050       300 3

               5              3080       300 3

               3              3030       300 3

               4              3060       300 3

               5              3090       300 3

               2               400        10 4

               3              4010       400 4

               4              4040       400 4

               5              4070       400 4

               6              4100       400 4

               3              4020       400 4

               4              4050       400 4

               5              4080       400 4

               3              4030       400 4

               4              4060       400 4

               5              4090       400 4

               2               500        10 5

               3              5010       500 5

               4              5040       500 5

               5              5070       500 5

               6              5100       500 5

               3              5020       500 5

               4              5050       500 5

               5              5080       500 5

               3              5030       500 5

               4              5060       500 5

               5              5090       500 5





share|improve this answer


























  • Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:41











  • Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

    – alexgibbs
    Nov 22 '18 at 1:33











  • please look at my update to the original question :)

    – Katherine Reed
    Nov 22 '18 at 2:18











  • Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

    – alexgibbs
    Nov 22 '18 at 3:50











  • Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

    – alexgibbs
    Nov 22 '18 at 4:00












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421964%2fhierarchical-query-limiting-number-of-elements-per-level%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














Here is how I would do this.



First the test data (I included enough employees to illustrate the point, but not quite 10 employees for each mid-level manager). I left out the phone number as irrelevant to the problem at hand.



create table employee_table(employee_number, name, manager, department) as

    select 1001, 'Big Boss', null, 100 from dual union all

    select 1100, 'Beth Mgr', 1001, 100 from dual union all

    select 1101, 'Jim'     , 1100, 100 from dual union all

    select 1102, 'Jackie'  , 1100, 100 from dual union all

    select 1103, 'Helen'   , 1100, 100 from dual union all

    select 1104, 'Tom'     , 1100, 100 from dual union all

    select 1105, 'Vance'   , 1100, 100 from dual union all

    select 1106, 'Rosa'    , 1100, 100 from dual union all

    select 1107, 'Chuck'   , 1100, 100 from dual union all

    select 1200, 'Duck Mgr', 1001, 200 from dual union all

    select 1201, 'Danny'   , 1200, 200 from dual union all

    select 1202, 'Henry'   , 1200, 200 from dual union all

    select 1203, 'Mac'     , 1200, 200 from dual union all

    select 1204, 'Hassan'  , 1200, 200 from dual union all

    select 1205, 'Ann'     , 1200, 200 from dual union all

    select 1300, 'Adam Mgr', 1001, 300 from dual union all

    select 1301, 'Wendy'   , 1300, 300 from dual

;


Then the query. I want the output to follow the "hierarchical order" (as it would be if we didn't have to mess with the levels). For that, I run the hierarchical query first, I capture ROWNUM for ordering of the final results, and I modify the level in the outer query. Note that I use LVL as column name; LEVEL is a reserved word, so it should not be used as column name.



select   case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,

         employee_number, name, manager, department

from     (

          select     level as lvl, employee_number, name, manager, department,

                     rownum as ord, 

                     row_number() over 

                        (partition by manager order by employee_number) as rn

          from       employee_table

          start with manager is null

          connect by prior employee_number = manager

         )

order by ord

;


OUTPUT:



       LVL EMPLOYEE_NUMBER NAME        MANAGER DEPARTMENT

---------- --------------- -------- ---------- ----------

         1            1001 Big Boss                   100

         2            1100 Beth Mgr       1001        100

         3            1101 Jim            1100        100

         3            1102 Jackie         1100        100

         3            1103 Helen          1100        100

         4            1104 Tom            1100        100

         4            1105 Vance          1100        100

         4            1106 Rosa           1100        100

         5            1107 Chuck          1100        100

         2            1200 Duck Mgr       1001        200

         3            1201 Danny          1200        200

         3            1202 Henry          1200        200

         3            1203 Mac            1200        200

         4            1204 Hassan         1200        200

         4            1205 Ann            1200        200

         2            1300 Adam Mgr       1001        300

         3            1301 Wendy          1300        300





share|improve this answer
























  • You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:39











  • Yep, no problem - adapt to your needs and come back if you have follow-up questions.

    – mathguy
    Nov 22 '18 at 0:49











  • please look at my update in the original question :)

    – Katherine Reed
    Nov 22 '18 at 1:50


















2














Here is how I would do this.



First the test data (I included enough employees to illustrate the point, but not quite 10 employees for each mid-level manager). I left out the phone number as irrelevant to the problem at hand.



create table employee_table(employee_number, name, manager, department) as

    select 1001, 'Big Boss', null, 100 from dual union all

    select 1100, 'Beth Mgr', 1001, 100 from dual union all

    select 1101, 'Jim'     , 1100, 100 from dual union all

    select 1102, 'Jackie'  , 1100, 100 from dual union all

    select 1103, 'Helen'   , 1100, 100 from dual union all

    select 1104, 'Tom'     , 1100, 100 from dual union all

    select 1105, 'Vance'   , 1100, 100 from dual union all

    select 1106, 'Rosa'    , 1100, 100 from dual union all

    select 1107, 'Chuck'   , 1100, 100 from dual union all

    select 1200, 'Duck Mgr', 1001, 200 from dual union all

    select 1201, 'Danny'   , 1200, 200 from dual union all

    select 1202, 'Henry'   , 1200, 200 from dual union all

    select 1203, 'Mac'     , 1200, 200 from dual union all

    select 1204, 'Hassan'  , 1200, 200 from dual union all

    select 1205, 'Ann'     , 1200, 200 from dual union all

    select 1300, 'Adam Mgr', 1001, 300 from dual union all

    select 1301, 'Wendy'   , 1300, 300 from dual

;


Then the query. I want the output to follow the "hierarchical order" (as it would be if we didn't have to mess with the levels). For that, I run the hierarchical query first, I capture ROWNUM for ordering of the final results, and I modify the level in the outer query. Note that I use LVL as column name; LEVEL is a reserved word, so it should not be used as column name.



select   case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,

         employee_number, name, manager, department

from     (

          select     level as lvl, employee_number, name, manager, department,

                     rownum as ord, 

                     row_number() over 

                        (partition by manager order by employee_number) as rn

          from       employee_table

          start with manager is null

          connect by prior employee_number = manager

         )

order by ord

;


OUTPUT:



       LVL EMPLOYEE_NUMBER NAME        MANAGER DEPARTMENT

---------- --------------- -------- ---------- ----------

         1            1001 Big Boss                   100

         2            1100 Beth Mgr       1001        100

         3            1101 Jim            1100        100

         3            1102 Jackie         1100        100

         3            1103 Helen          1100        100

         4            1104 Tom            1100        100

         4            1105 Vance          1100        100

         4            1106 Rosa           1100        100

         5            1107 Chuck          1100        100

         2            1200 Duck Mgr       1001        200

         3            1201 Danny          1200        200

         3            1202 Henry          1200        200

         3            1203 Mac            1200        200

         4            1204 Hassan         1200        200

         4            1205 Ann            1200        200

         2            1300 Adam Mgr       1001        300

         3            1301 Wendy          1300        300





share|improve this answer
























  • You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:39











  • Yep, no problem - adapt to your needs and come back if you have follow-up questions.

    – mathguy
    Nov 22 '18 at 0:49











  • please look at my update in the original question :)

    – Katherine Reed
    Nov 22 '18 at 1:50
















2












2








2







Here is how I would do this.



First the test data (I included enough employees to illustrate the point, but not quite 10 employees for each mid-level manager). I left out the phone number as irrelevant to the problem at hand.



create table employee_table(employee_number, name, manager, department) as

    select 1001, 'Big Boss', null, 100 from dual union all

    select 1100, 'Beth Mgr', 1001, 100 from dual union all

    select 1101, 'Jim'     , 1100, 100 from dual union all

    select 1102, 'Jackie'  , 1100, 100 from dual union all

    select 1103, 'Helen'   , 1100, 100 from dual union all

    select 1104, 'Tom'     , 1100, 100 from dual union all

    select 1105, 'Vance'   , 1100, 100 from dual union all

    select 1106, 'Rosa'    , 1100, 100 from dual union all

    select 1107, 'Chuck'   , 1100, 100 from dual union all

    select 1200, 'Duck Mgr', 1001, 200 from dual union all

    select 1201, 'Danny'   , 1200, 200 from dual union all

    select 1202, 'Henry'   , 1200, 200 from dual union all

    select 1203, 'Mac'     , 1200, 200 from dual union all

    select 1204, 'Hassan'  , 1200, 200 from dual union all

    select 1205, 'Ann'     , 1200, 200 from dual union all

    select 1300, 'Adam Mgr', 1001, 300 from dual union all

    select 1301, 'Wendy'   , 1300, 300 from dual

;


Then the query. I want the output to follow the "hierarchical order" (as it would be if we didn't have to mess with the levels). For that, I run the hierarchical query first, I capture ROWNUM for ordering of the final results, and I modify the level in the outer query. Note that I use LVL as column name; LEVEL is a reserved word, so it should not be used as column name.



select   case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,

         employee_number, name, manager, department

from     (

          select     level as lvl, employee_number, name, manager, department,

                     rownum as ord, 

                     row_number() over 

                        (partition by manager order by employee_number) as rn

          from       employee_table

          start with manager is null

          connect by prior employee_number = manager

         )

order by ord

;


OUTPUT:



       LVL EMPLOYEE_NUMBER NAME        MANAGER DEPARTMENT

---------- --------------- -------- ---------- ----------

         1            1001 Big Boss                   100

         2            1100 Beth Mgr       1001        100

         3            1101 Jim            1100        100

         3            1102 Jackie         1100        100

         3            1103 Helen          1100        100

         4            1104 Tom            1100        100

         4            1105 Vance          1100        100

         4            1106 Rosa           1100        100

         5            1107 Chuck          1100        100

         2            1200 Duck Mgr       1001        200

         3            1201 Danny          1200        200

         3            1202 Henry          1200        200

         3            1203 Mac            1200        200

         4            1204 Hassan         1200        200

         4            1205 Ann            1200        200

         2            1300 Adam Mgr       1001        300

         3            1301 Wendy          1300        300





share|improve this answer













Here is how I would do this.



First the test data (I included enough employees to illustrate the point, but not quite 10 employees for each mid-level manager). I left out the phone number as irrelevant to the problem at hand.



create table employee_table(employee_number, name, manager, department) as

    select 1001, 'Big Boss', null, 100 from dual union all

    select 1100, 'Beth Mgr', 1001, 100 from dual union all

    select 1101, 'Jim'     , 1100, 100 from dual union all

    select 1102, 'Jackie'  , 1100, 100 from dual union all

    select 1103, 'Helen'   , 1100, 100 from dual union all

    select 1104, 'Tom'     , 1100, 100 from dual union all

    select 1105, 'Vance'   , 1100, 100 from dual union all

    select 1106, 'Rosa'    , 1100, 100 from dual union all

    select 1107, 'Chuck'   , 1100, 100 from dual union all

    select 1200, 'Duck Mgr', 1001, 200 from dual union all

    select 1201, 'Danny'   , 1200, 200 from dual union all

    select 1202, 'Henry'   , 1200, 200 from dual union all

    select 1203, 'Mac'     , 1200, 200 from dual union all

    select 1204, 'Hassan'  , 1200, 200 from dual union all

    select 1205, 'Ann'     , 1200, 200 from dual union all

    select 1300, 'Adam Mgr', 1001, 300 from dual union all

    select 1301, 'Wendy'   , 1300, 300 from dual

;


Then the query. I want the output to follow the "hierarchical order" (as it would be if we didn't have to mess with the levels). For that, I run the hierarchical query first, I capture ROWNUM for ordering of the final results, and I modify the level in the outer query. Note that I use LVL as column name; LEVEL is a reserved word, so it should not be used as column name.



select   case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,

         employee_number, name, manager, department

from     (

          select     level as lvl, employee_number, name, manager, department,

                     rownum as ord, 

                     row_number() over 

                        (partition by manager order by employee_number) as rn

          from       employee_table

          start with manager is null

          connect by prior employee_number = manager

         )

order by ord

;


OUTPUT:



       LVL EMPLOYEE_NUMBER NAME        MANAGER DEPARTMENT

---------- --------------- -------- ---------- ----------

         1            1001 Big Boss                   100

         2            1100 Beth Mgr       1001        100

         3            1101 Jim            1100        100

         3            1102 Jackie         1100        100

         3            1103 Helen          1100        100

         4            1104 Tom            1100        100

         4            1105 Vance          1100        100

         4            1106 Rosa           1100        100

         5            1107 Chuck          1100        100

         2            1200 Duck Mgr       1001        200

         3            1201 Danny          1200        200

         3            1202 Henry          1200        200

         3            1203 Mac            1200        200

         4            1204 Hassan         1200        200

         4            1205 Ann            1200        200

         2            1300 Adam Mgr       1001        300

         3            1301 Wendy          1300        300






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 22 '18 at 0:34









mathguymathguy

26.6k51737




26.6k51737













  • You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:39











  • Yep, no problem - adapt to your needs and come back if you have follow-up questions.

    – mathguy
    Nov 22 '18 at 0:49











  • please look at my update in the original question :)

    – Katherine Reed
    Nov 22 '18 at 1:50





















  • You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:39











  • Yep, no problem - adapt to your needs and come back if you have follow-up questions.

    – mathguy
    Nov 22 '18 at 0:49











  • please look at my update in the original question :)

    – Katherine Reed
    Nov 22 '18 at 1:50



















You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

– Katherine Reed
Nov 22 '18 at 0:39





You bring up a good point, but I'm not sure yet which query will produce the exact output I am looking for. I need to spend some time tonight and tomorrow testing both queries with my data. I thank you for the work you put into this, and I will definitely return and comment with a more definitive response after I test your query. Thank you very much for your help!

– Katherine Reed
Nov 22 '18 at 0:39













Yep, no problem - adapt to your needs and come back if you have follow-up questions.

– mathguy
Nov 22 '18 at 0:49





Yep, no problem - adapt to your needs and come back if you have follow-up questions.

– mathguy
Nov 22 '18 at 0:49













please look at my update in the original question :)

– Katherine Reed
Nov 22 '18 at 1:50







please look at my update in the original question :)

– Katherine Reed
Nov 22 '18 at 1:50















0














Note: This has been edited significantly in response to an update in the question, and is uncertain/not-tested with apex for compatability



The below solution would just add section managers beyond the third at level 2. But for level 3+, if it does not matter how additional levels are assigned beyond the section managers, one can just assign them based on employee_id. With the update that each member of a group-of-three should have their pseudo-next-level nested directly below, here is an example that assigns pseudo-levels and sub-levels in groups of three, based on employee id, within a department.



Below is an example data set and query.



CREATE TABLE EMPLOYEE_TABLE (

  EMPLOYEE_NUMBER NUMBER,

  MANAGER NUMBER DEFAULT NULL,

  NAME CHARACTER VARYING(64 BYTE),

  DEPARTMENT CHARACTER VARYING(64 BYTE),

  PHONE CHARACTER VARYING(64 BYTE)

);





--Dept Manager

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (10,NULL, 1);

--Section Managers

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (200,10, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (300,10, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (400,10, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (500,10, 5);

-- Section Employees

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2010,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2020,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2030,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2040,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2050,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2060,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2070,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2080,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2090,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2100,200, 2);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3010,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3020,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3030,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3040,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3050,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3060,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3070,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3080,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3090,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3100,300, 3);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4010,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4020,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4030,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4040,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4050,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4060,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4070,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4080,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4090,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4100,400, 4);





INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5010,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5020,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5030,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5040,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5050,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5060,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5070,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5080,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5090,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5100,500, 5);

COMMIT;


Edited
Query: 



SELECT

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


Result:



  ADJUSTED_LEVEL   EMPLOYEE_NUMBER   MANAGER DEPARTMENT

               1                10           1

               2               200        10 2

               3              2010       200 2

               4              2040       200 2

               5              2070       200 2

               6              2100       200 2

               3              2020       200 2

               4              2050       200 2

               5              2080       200 2

               3              2030       200 2

               4              2060       200 2

               5              2090       200 2

               2               300        10 3

               3              3010       300 3

               4              3040       300 3

               5              3070       300 3

               6              3100       300 3

               3              3020       300 3

               4              3050       300 3

               5              3080       300 3

               3              3030       300 3

               4              3060       300 3

               5              3090       300 3

               2               400        10 4

               3              4010       400 4

               4              4040       400 4

               5              4070       400 4

               6              4100       400 4

               3              4020       400 4

               4              4050       400 4

               5              4080       400 4

               3              4030       400 4

               4              4060       400 4

               5              4090       400 4

               2               500        10 5

               3              5010       500 5

               4              5040       500 5

               5              5070       500 5

               6              5100       500 5

               3              5020       500 5

               4              5050       500 5

               5              5080       500 5

               3              5030       500 5

               4              5060       500 5

               5              5090       500 5





share|improve this answer


























  • Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:41











  • Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

    – alexgibbs
    Nov 22 '18 at 1:33











  • please look at my update to the original question :)

    – Katherine Reed
    Nov 22 '18 at 2:18











  • Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

    – alexgibbs
    Nov 22 '18 at 3:50











  • Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

    – alexgibbs
    Nov 22 '18 at 4:00
















0














Note: This has been edited significantly in response to an update in the question, and is uncertain/not-tested with apex for compatability



The below solution would just add section managers beyond the third at level 2. But for level 3+, if it does not matter how additional levels are assigned beyond the section managers, one can just assign them based on employee_id. With the update that each member of a group-of-three should have their pseudo-next-level nested directly below, here is an example that assigns pseudo-levels and sub-levels in groups of three, based on employee id, within a department.



Below is an example data set and query.



CREATE TABLE EMPLOYEE_TABLE (

  EMPLOYEE_NUMBER NUMBER,

  MANAGER NUMBER DEFAULT NULL,

  NAME CHARACTER VARYING(64 BYTE),

  DEPARTMENT CHARACTER VARYING(64 BYTE),

  PHONE CHARACTER VARYING(64 BYTE)

);





--Dept Manager

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (10,NULL, 1);

--Section Managers

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (200,10, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (300,10, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (400,10, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (500,10, 5);

-- Section Employees

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2010,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2020,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2030,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2040,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2050,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2060,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2070,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2080,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2090,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2100,200, 2);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3010,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3020,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3030,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3040,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3050,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3060,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3070,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3080,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3090,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3100,300, 3);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4010,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4020,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4030,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4040,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4050,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4060,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4070,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4080,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4090,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4100,400, 4);





INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5010,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5020,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5030,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5040,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5050,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5060,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5070,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5080,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5090,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5100,500, 5);

COMMIT;


Edited
Query: 



SELECT

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


Result:



  ADJUSTED_LEVEL   EMPLOYEE_NUMBER   MANAGER DEPARTMENT

               1                10           1

               2               200        10 2

               3              2010       200 2

               4              2040       200 2

               5              2070       200 2

               6              2100       200 2

               3              2020       200 2

               4              2050       200 2

               5              2080       200 2

               3              2030       200 2

               4              2060       200 2

               5              2090       200 2

               2               300        10 3

               3              3010       300 3

               4              3040       300 3

               5              3070       300 3

               6              3100       300 3

               3              3020       300 3

               4              3050       300 3

               5              3080       300 3

               3              3030       300 3

               4              3060       300 3

               5              3090       300 3

               2               400        10 4

               3              4010       400 4

               4              4040       400 4

               5              4070       400 4

               6              4100       400 4

               3              4020       400 4

               4              4050       400 4

               5              4080       400 4

               3              4030       400 4

               4              4060       400 4

               5              4090       400 4

               2               500        10 5

               3              5010       500 5

               4              5040       500 5

               5              5070       500 5

               6              5100       500 5

               3              5020       500 5

               4              5050       500 5

               5              5080       500 5

               3              5030       500 5

               4              5060       500 5

               5              5090       500 5





share|improve this answer


























  • Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:41











  • Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

    – alexgibbs
    Nov 22 '18 at 1:33











  • please look at my update to the original question :)

    – Katherine Reed
    Nov 22 '18 at 2:18











  • Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

    – alexgibbs
    Nov 22 '18 at 3:50











  • Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

    – alexgibbs
    Nov 22 '18 at 4:00














0












0








0







Note: This has been edited significantly in response to an update in the question, and is uncertain/not-tested with apex for compatability



The below solution would just add section managers beyond the third at level 2. But for level 3+, if it does not matter how additional levels are assigned beyond the section managers, one can just assign them based on employee_id. With the update that each member of a group-of-three should have their pseudo-next-level nested directly below, here is an example that assigns pseudo-levels and sub-levels in groups of three, based on employee id, within a department.



Below is an example data set and query.



CREATE TABLE EMPLOYEE_TABLE (

  EMPLOYEE_NUMBER NUMBER,

  MANAGER NUMBER DEFAULT NULL,

  NAME CHARACTER VARYING(64 BYTE),

  DEPARTMENT CHARACTER VARYING(64 BYTE),

  PHONE CHARACTER VARYING(64 BYTE)

);





--Dept Manager

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (10,NULL, 1);

--Section Managers

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (200,10, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (300,10, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (400,10, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (500,10, 5);

-- Section Employees

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2010,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2020,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2030,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2040,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2050,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2060,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2070,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2080,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2090,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2100,200, 2);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3010,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3020,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3030,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3040,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3050,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3060,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3070,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3080,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3090,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3100,300, 3);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4010,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4020,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4030,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4040,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4050,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4060,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4070,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4080,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4090,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4100,400, 4);





INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5010,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5020,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5030,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5040,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5050,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5060,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5070,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5080,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5090,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5100,500, 5);

COMMIT;


Edited
Query: 



SELECT

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


Result:



  ADJUSTED_LEVEL   EMPLOYEE_NUMBER   MANAGER DEPARTMENT

               1                10           1

               2               200        10 2

               3              2010       200 2

               4              2040       200 2

               5              2070       200 2

               6              2100       200 2

               3              2020       200 2

               4              2050       200 2

               5              2080       200 2

               3              2030       200 2

               4              2060       200 2

               5              2090       200 2

               2               300        10 3

               3              3010       300 3

               4              3040       300 3

               5              3070       300 3

               6              3100       300 3

               3              3020       300 3

               4              3050       300 3

               5              3080       300 3

               3              3030       300 3

               4              3060       300 3

               5              3090       300 3

               2               400        10 4

               3              4010       400 4

               4              4040       400 4

               5              4070       400 4

               6              4100       400 4

               3              4020       400 4

               4              4050       400 4

               5              4080       400 4

               3              4030       400 4

               4              4060       400 4

               5              4090       400 4

               2               500        10 5

               3              5010       500 5

               4              5040       500 5

               5              5070       500 5

               6              5100       500 5

               3              5020       500 5

               4              5050       500 5

               5              5080       500 5

               3              5030       500 5

               4              5060       500 5

               5              5090       500 5





share|improve this answer















Note: This has been edited significantly in response to an update in the question, and is uncertain/not-tested with apex for compatability



The below solution would just add section managers beyond the third at level 2. But for level 3+, if it does not matter how additional levels are assigned beyond the section managers, one can just assign them based on employee_id. With the update that each member of a group-of-three should have their pseudo-next-level nested directly below, here is an example that assigns pseudo-levels and sub-levels in groups of three, based on employee id, within a department.



Below is an example data set and query.



CREATE TABLE EMPLOYEE_TABLE (

  EMPLOYEE_NUMBER NUMBER,

  MANAGER NUMBER DEFAULT NULL,

  NAME CHARACTER VARYING(64 BYTE),

  DEPARTMENT CHARACTER VARYING(64 BYTE),

  PHONE CHARACTER VARYING(64 BYTE)

);





--Dept Manager

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (10,NULL, 1);

--Section Managers

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (200,10, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (300,10, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (400,10, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (500,10, 5);

-- Section Employees

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2010,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2020,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2030,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2040,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2050,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2060,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2070,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2080,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2090,200, 2);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (2100,200, 2);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3010,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3020,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3030,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3040,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3050,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3060,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3070,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3080,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3090,300, 3);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (3100,300, 3);



INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4010,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4020,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4030,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4040,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4050,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4060,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4070,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4080,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4090,400, 4);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (4100,400, 4);





INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5010,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5020,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5030,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5040,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5050,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5060,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5070,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5080,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5090,500, 5);

INSERT INTO EMPLOYEE_TABLE(EMPLOYEE_NUMBER, MANAGER, DEPARTMENT) VALUES (5100,500, 5);

COMMIT;


Edited
Query: 



SELECT

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE 3 + FLOOR((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC ) - 1) / 3) END AS ADJUSTED_LEVEL,

EMPLOYEE_NUMBER,

MANAGER,

DEPARTMENT

FROM EMPLOYEE_TABLE

START WITH MANAGER IS NULL

CONNECT BY PRIOR EMPLOYEE_NUMBER = MANAGER

ORDER BY

NVL2(MANAGER,1,0) ASC,

DEPARTMENT ASC,

CASE WHEN LEVEL < 3

     THEN LEVEL

     ELSE (MOD((DENSE_RANK() OVER (PARTITION BY MANAGER ORDER BY EMPLOYEE_NUMBER ASC )) - 1,3) + 3) END ASC,

ADJUSTED_LEVEL ASC;


Result:



  ADJUSTED_LEVEL   EMPLOYEE_NUMBER   MANAGER DEPARTMENT

               1                10           1

               2               200        10 2

               3              2010       200 2

               4              2040       200 2

               5              2070       200 2

               6              2100       200 2

               3              2020       200 2

               4              2050       200 2

               5              2080       200 2

               3              2030       200 2

               4              2060       200 2

               5              2090       200 2

               2               300        10 3

               3              3010       300 3

               4              3040       300 3

               5              3070       300 3

               6              3100       300 3

               3              3020       300 3

               4              3050       300 3

               5              3080       300 3

               3              3030       300 3

               4              3060       300 3

               5              3090       300 3

               2               400        10 4

               3              4010       400 4

               4              4040       400 4

               5              4070       400 4

               6              4100       400 4

               3              4020       400 4

               4              4050       400 4

               5              4080       400 4

               3              4030       400 4

               4              4060       400 4

               5              4090       400 4

               2               500        10 5

               3              5010       500 5

               4              5040       500 5

               5              5070       500 5

               6              5100       500 5

               3              5020       500 5

               4              5050       500 5

               5              5080       500 5

               3              5030       500 5

               4              5060       500 5

               5              5090       500 5






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 '18 at 3:59

























answered Nov 22 '18 at 0:20









alexgibbsalexgibbs

1,62321015




1,62321015













  • Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:41











  • Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

    – alexgibbs
    Nov 22 '18 at 1:33











  • please look at my update to the original question :)

    – Katherine Reed
    Nov 22 '18 at 2:18











  • Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

    – alexgibbs
    Nov 22 '18 at 3:50











  • Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

    – alexgibbs
    Nov 22 '18 at 4:00



















  • Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

    – Katherine Reed
    Nov 22 '18 at 0:41











  • Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

    – alexgibbs
    Nov 22 '18 at 1:33











  • please look at my update to the original question :)

    – Katherine Reed
    Nov 22 '18 at 2:18











  • Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

    – alexgibbs
    Nov 22 '18 at 3:50











  • Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

    – alexgibbs
    Nov 22 '18 at 4:00

















Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

– Katherine Reed
Nov 22 '18 at 0:41





Thank you very much for your very detailed and quick solution, @alexgibbs. I will spend time tonight and tomorrow testing your query with my dataset to ensure that it produces the exact output I am looking for, and then I will return to comment with a more definitive response to your solution. Thank you so much for your help!

– Katherine Reed
Nov 22 '18 at 0:41













Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

– alexgibbs
Nov 22 '18 at 1:33





Thanks Katherine. I would echo @mathguy - if any follow up would be helpful just let me know. Meantime, I believe mathguy's solution has a nicer, cleaner layout and a natural sort; I would recommend that solution.

– alexgibbs
Nov 22 '18 at 1:33













please look at my update to the original question :)

– Katherine Reed
Nov 22 '18 at 2:18





please look at my update to the original question :)

– Katherine Reed
Nov 22 '18 at 2:18













Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

– alexgibbs
Nov 22 '18 at 3:50





Thanks Katherine. I don't use apex, so I can't make a confident update here. I'll add a provisional edit that just re-assigns and re-sorts, but apologies in advance it may not be what you are looking for. I would also defer largely to @mathguy as your update uses data from mathguy's answer. Nonetheless I'll update now with a best guess.

– alexgibbs
Nov 22 '18 at 3:50













Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

– alexgibbs
Nov 22 '18 at 4:00





Ok @KatherineReed updates made. I'd appreciate any feedback whether this gets at the kind of layout you were looking for. Thanks!

– alexgibbs
Nov 22 '18 at 4:00


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421964%2fhierarchical-query-limiting-number-of-elements-per-level%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Biblatex bibliography style without URLs when DOI exists (in Overleaf with Zotero bibliography)

Image
2 2 I have a Zotero bibliography in Overleaf. I use biblatex . usepackage[backend=biber,citestyle=authoryear]{biblatex} addbibresource{zoteroALH.bib} Bibliography entries look like: @book{de_saint-gervais_uniformization_2016, title = {Uniformization of {Riemann} {Surfaces}}, isbn = {978-3-03719-145-3}, url = {https://www.ems-ph.org/books/book.php?proj_nr=198&srch=series%7Chem}, urldate = {2019-02-03}, author = {de Saint-Gervais, Paul Henri}, year = {2016}, doi = {10.4171/145} } In the bibliography down the paper the reference shows like this: There is the DOI, great, but also the URL which is unnecessary when the DOI is there. How can I remove the URL when the DOI is present? biblatex bibliographies overleaf zote
Read more

ComboBox Display Member on multiple fields

Image
2 I'm trying to set the .DisplayMember property of a ComboBox in C#, but I want to bind it to multiple columns in the .DataSouce . My SQL looks like this: SELECT PersNbr, PersFirstName, PersMiddleName, PersLastName FROM Pers WHERE PersNbr = :persNbr; I'm saving this query in a DataTable so each column selected has it's own column in the Datatable . I want to make the .DisplayMember a combination of PersFirstName + PersMiddleName + PersLastName so their full name appears like this: comboBox.DisplayMemeber = "PersFirstName" + "PersMiddleName" + "PersLastName" I know I can just to this on the query: SELECT PersNbr, (PersFirstName || PersMiddleName || PersLastName) PersName and then just do this: comboBox.DisplayMember = "PersName"; but I
Read more

Is it possible to collect Nectar points via Trainline?

Image
2 Since I am not eligible for any Railcard, the only available discount/bonus scheme is Nectar Card for me. GWR, SWR, LNER and Virgin Trains allow to collect Nectar points and I have already linked a card to my GWR account. A friend suggested me to use Trainline App, because it can search for bus tickets either. I had a look at the application, but could not see any option for Nectar. Is there a way to collect Nectar points when I buy tickets via Trainline? uk trains loyalty-programs trainline share | improve this question asked Dec 2 '18 at 15:50 ahmedus 3,224 5 19 49
Read more