**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.

### Like this:

Like Loading...

*Related*

Ashwin Kumar PadhyApril 25, 2016 / 11:39 pmselect 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 PadhyApril 27, 2016 / 9:32 amselect 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;

AshwiniApril 28, 2016 / 5:34 amwith 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 HilkevichMay 2, 2016 / 7:18 amFirst 2 solutions (that look the same) result in :

ERROR at line 5:

ORA-00920: invalid relational operator

The last solution is accepted

Krishna JamalApril 27, 2016 / 1:07 pmCol “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 HilkevichMay 2, 2016 / 7:24 amAccepted; though it is not clear why we need the KEEP clause

Krishna JamalApril 27, 2016 / 1:59 pmSELECT 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;

AshwiniApril 28, 2016 / 5:15 amwith 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;

JAMESOApril 28, 2016 / 2:29 pmSELECT 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

JAMESOApril 28, 2016 / 2:28 pmSELECT 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

jolivercomblogApril 28, 2016 / 2:47 pmWhen 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

Krishna JamalApril 30, 2016 / 9:54 amSELECT 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;