## Puzzle of the Week #9

Puzzle: All employees are sorted by employee number and need to be split in 3 groups equal in size (as close as possible).  Employees with the smallest numbers will get into the 1st group, the 2nd group will have employees with the next (bigger) range of numbers, etc.

Write a single SELECT statement (against emp table) that would show group number, the range of employee numbers, and the size of each group.

Expected Result:

```Group RANGE           Count
----- ---------- ----------
1 7369-7654           5
2 7698-7844           5
3 7876-7934           4```

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

### 13 thoughts on “Puzzle of the Week #9”

1. Ashwin Kumar Padhy April 25, 2016 / 11:39 pm

select 1 “Group”,min(rownum)||’-‘||max(rownum) “Range”,COUNT(rownum) “Count” from
(select count(empno) X,CEIL(count(empno)/3) Y from emp )connect by level <=Y
union
select 2 "Group",min(rownum+Y)||'-'||max(rownum+Y) "Range",COUNT(rownum+y) "Count" from
(select count(empno) X,CEIL(count(empno)/3) Y from emp) connect by level 0 then null else rownum+(2*Y) end)||’-‘||max(case when (rownum+(2*Y)) -X > 0 then null else rownum+(2*Y) end) “Range”,
COUNT(case when (rownum+(2*Y)) -X > 0 then null else rownum+(2*Y) end) “Count” from
(select count(empno) X,CEIL(count(empno)/3) Y from emp) connect by level <=Y;

• Ashwin Kumar Padhy April 27, 2016 / 9:32 am

select 1 “Group”,min(rownum)||’-‘||max(rownum) “Range”,COUNT(rownum) “Count” from
(select count(empno) X,CEIL(count(empno)/3) Y from emp )connect by level <=Y
union
select 2 "Group",min(rownum+Y)||'-'||max(rownum+Y) "Range",COUNT(rownum+y) "Count" from
(select count(empno) X,CEIL(count(empno)/3) Y from emp) connect by level 0 then null else rownum+(2*Y) end)||’-‘||max(case when (rownum+(2*Y)) -X > 0 then null else rownum+(2*Y) end) “Range”,
COUNT(case when (rownum+(2*Y)) -X > 0 then null else rownum+(2*Y) end) “Count” from
(select count(empno) X,CEIL(count(empno)/3) Y from emp) connect by level <=Y;

• Ashwini April 28, 2016 / 5:34 am

with cnt_tabl as (

select l,cnt,(case when l=1 then 1 when l =2 then lag(cnt,1) over (order by l)+1 when l=3 then lag(cnt,1) over (order by l)+lag(cnt,2) over (order by l)+1 end) l_cnt,
(case when l=1 then cnt when l=2 then cnt + lag(cnt,1)over (order by l) when l=3 then x end) u_cnt
from (
select level l,decode(level,3,f,2,(case when ext =2 then f+1 when ext =1 then f when ext=0 then f end),1, case when ext =0 then f else f+1 end)
cnt,x
from (
select floor(x/3) f ,case when (round(x/3) -x/3 ) > 0 then 2
when (round(x/3) -x/3) < 0 then 1 else 0 end ext,x from (select count(1) x from emp)) connect by level<=3)
order by l
)

select l,range,cnt from(
select l,empno|| '-' || lead(empno,1) over (partition by l order by empno) range,cnt,row_number() over (partition by l order by empno) r
from (
select empno,rownum rn from emp order by empno),cnt_tabl
where rn = l_cnt or rn =u_cnt order by l,empno) where r=1;

• Zahar Hilkevich May 2, 2016 / 7:18 am

First 2 solutions (that look the same) result in :
ERROR at line 5:
ORA-00920: invalid relational operator

The last solution is accepted

2. Krishna Jamal April 27, 2016 / 1:07 pm

Col “Range” Format a30
SELECT Grp “Group”,
MIN(Empno) KEEP(DENSE_RANK FIRST ORDER BY Empno)||’-‘||MAX(Empno) KEEP(DENSE_RANK LAST ORDER BY Empno) “Range”,
COUNT(Grp) “Count”
FROM(SELECT Empno, NTILE(3) OVER(ORDER BY Empno) Grp FROM Emp)
GROUP BY Grp;

• Zahar Hilkevich May 2, 2016 / 7:24 am

Accepted; though it is not clear why we need the KEEP clause

3. Krishna Jamal April 27, 2016 / 1:59 pm

SELECT Grp “Group”,
MIN(Empno) KEEP(DENSE_RANK FIRST ORDER BY Empno)||’-‘||MAX

(Empno) KEEP(DENSE_RANK LAST ORDER BY Empno) “Range”,
COUNT(Grp) “Count”
FROM(SELECT Empno, NTILE(3) OVER(ORDER BY Empno) Grp FROM Emp)
GROUP BY Grp;

4. Ashwini April 28, 2016 / 5:15 am

with cnt_tabl as (

select l,cnt,(case when l=1 then 1 when l =2 then lag(cnt,1) over (order by l)+1 when l=3 then lag(cnt,1) over (order by l)+lag(cnt,2) over (order by l)+1 end) l_cnt,
(case when l=1 then cnt when l=2 then cnt + lag(cnt,1)over (order by l) when l=3 then x end) u_cnt
from (
select level l,decode(level,3,f,2,(case when ext =2 then f+1 when ext =1 then f when ext=0 then f end),1, case when ext =0 then f else f+1 end)
cnt,x
from (
select floor(x/3) f ,case when (round(x/3) -x/3 ) > 0 then 2
when (round(x/3) -x/3) < 0 then 1 else 0 end ext,x from (select count(1) x from emp)) connect by level<=3)
order by l
)

select l,range,cnt from(
select l,empno|| '-' || lead(empno,1) over (partition by l order by empno) range,cnt,row_number() over (partition by l order by empno) r
from (
select empno,rownum rn from emp order by empno),cnt_tabl
where rn = l_cnt or rn =u_cnt order by l,empno) where r=1;

• JAMESO April 28, 2016 / 2:29 pm

SELECT SUBSTR(GROUPY, 1,5) “Group”,CONCAT( CONCAT(MIN(EMPY), ‘-‘), MAX(EMPY)) “RANGE”, COUNT(*) “Count”
FROM
(SELECT EMPNO EMPY, NTILE (3) OVER (ORDER BY ROWNUM) GROUPY
FROM EMP)
GROUP BY GROUPY

5. JAMESO April 28, 2016 / 2:28 pm

SELECT SUBSTR(GROUPY, 1,5) “Group”,CONCAT( CONCAT(MIN(EMPY), ‘-‘), MAX(EMPY)) “RANGE”, COUNT(*) “Count”
FROM
(SELECT EMPNO EMPY, NTILE (3) OVER (ORDER BY ROWNUM) GROUPY
FROM EMP)
GROUP BY GROUPY

6. jolivercomblog April 28, 2016 / 2:47 pm

When I post my comment/SQL, I don’t see it get posted

SELECT SUBSTR(GROUPY, 1,5) “Group”,CONCAT( CONCAT(MIN(EMPY), ‘-‘), MAX(EMPY)) “RANGE”, COUNT(*) “Count”
FROM
(SELECT EMPNO EMPY, NTILE (3) OVER (ORDER BY ROWNUM) GROUPY
FROM EMP)
GROUP BY GROUPY

7. Krishna Jamal April 30, 2016 / 9:54 am

SELECT Grp “Group”, MIN(Empno)||’-‘||MAX(Empno) “Range”, COUNT(Grp) “Count”
FROM( SELECT Empno, CEIL(ROWNUM/Rn) Grp
FROM Emp, (SELECT CEIL(COUNT(*)/3) AS Rn FROM Emp)
) GROUP BY Grp;