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.

 

Advertisements

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s