Solutions to Puzzle of the Week #9

Puzzle of the Week #9:

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

Solutions:

#1: Using NTile Analytic function

WITH x AS (
SELECT empno, NTILE(3)OVER(ORDER BY empno) nt
FROM emp
)
SELECT nt "Group", MIN(empno)||'-'||MAX(empno) "Range", COUNT(*) "Count"
FROM x
GROUP BY nt
ORDER BY 1;

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

#2: Simulating NTile function

WITH x AS (
SELECT empno, CEIL(ROW_NUMBER()OVER(ORDER BY empno)/CEIL(COUNT(*)OVER()/3)) nt
FROM emp
)
SELECT nt "Group", MIN(empno)||'-'||MAX(empno) "Range", COUNT(*) "Count"
FROM x
GROUP BY nt
ORDER BY 1;

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

 

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

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