How to group rows in fixed group size with MSSQL

  • We often want to group data in fixed group size to make it easier to read, such as: green lines or separation lines...
  • Microsoft SQL Server 2005 and Microsoft SQL Server 2008 have the built-in function: ntile see: How to group items into a fixed number of bucket.
  • The problem with ntile is that you can't control it, only Microsoft controls it, and it does it's best approximation, it may not be the result that you want.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT deptno, projno, projname
  4. FROM projects
  5. WHERE deptno < 'e'
  6. ORDER BY deptno, projno;
  7. go
deptno projno projname
------ ------ ------------------------
B01    PL2100 WELD LINE PLANNING
C01    IF1000 QUERY SERVICES
C01    IF2000 USER EDUCATION
D01    AD3100 ADMIN SERVICES
D01    MA2100 WELDLINE AUTOMATION
D11    MA2110 W L PROGRAMMING
D11    MA2111 W L PROGRAM DESIGN
D11    MA2112 W L ROBOT DESIGN
D11    MA2113 W L PRODCONT PROGS
D21    AD3110 GENERAL ADMINSYSTEMS
D21    AD3111 PAYROLL PROGRAMMING
D21    AD3112 PERSONNEL PROGRAMMING
D21    AD3113 ACCOUNT PROGRAMMING

(13 row(s) affected)
  • It's very important that we have an odd number of rows to demonstrate the difference between ntile and ceiling calculations.

Grouping rows in fixed group size

  1. SELECT CEILING(row_number() OVER (ORDER BY deptno,projno)/5.0) AS 'Grouping',
  2.        deptno, projno, projname
  3. FROM projects
  4. WHERE deptno < 'e'
  5. ORDER BY deptno, projno;
  6. go
Grouping                                deptno projno projname
--------------------------------------- ------ ------ ------------------------
1                                       B01    PL2100 WELD LINE PLANNING
1                                       C01    IF1000 QUERY SERVICES
1                                       C01    IF2000 USER EDUCATION
1                                       D01    AD3100 ADMIN SERVICES
1                                       D01    MA2100 WELDLINE AUTOMATION
2                                       D11    MA2110 W L PROGRAMMING
2                                       D11    MA2111 W L PROGRAM DESIGN
2                                       D11    MA2112 W L ROBOT DESIGN
2                                       D11    MA2113 W L PRODCONT PROGS
2                                       D21    AD3110 GENERAL ADMINSYSTEMS
3                                       D21    AD3111 PAYROLL PROGRAMMING
3                                       D21    AD3112 PERSONNEL PROGRAMMING
3                                       D21    AD3113 ACCOUNT PROGRAMMING

(13 row(s) affected)
  • Notice that the ceiling calculation of: ceiling(row_number() over (order by deptno,projno)/5.0) groups the rows in exactly 5 rows, and the last group has the rest which most of the time is not the exact amount.
  • But that's how most people think about the buckets of equal size.

Ceiling vs Ntile

  1. SELECT ntile(4) OVER(ORDER BY deptno, projno) AS 'Ntile grouping',
  2.        CEILING(row_number() OVER (ORDER BY deptno,projno)/5.0) AS 'Ceiling calc',
  3.        deptno, projno, projname
  4. FROM projects
  5. WHERE deptno < 'e'
  6. ORDER BY deptno, projno;
  7. go
Ntile grouping       Ceiling calc                            deptno projno projname
-------------------- --------------------------------------- ------ ------ ------------------------
1                    1                                       B01    PL2100 WELD LINE PLANNING
1                    1                                       C01    IF1000 QUERY SERVICES
1                    1                                       C01    IF2000 USER EDUCATION
1                    1                                       D01    AD3100 ADMIN SERVICES
2                    1                                       D01    MA2100 WELDLINE AUTOMATION
2                    2                                       D11    MA2110 W L PROGRAMMING
2                    2                                       D11    MA2111 W L PROGRAM DESIGN
3                    2                                       D11    MA2112 W L ROBOT DESIGN
3                    2                                       D11    MA2113 W L PRODCONT PROGS
3                    2                                       D21    AD3110 GENERAL ADMINSYSTEMS
4                    3                                       D21    AD3111 PAYROLL PROGRAMMING
4                    3                                       D21    AD3112 PERSONNEL PROGRAMMING
4                    3                                       D21    AD3113 ACCOUNT PROGRAMMING

(13 row(s) affected)
  • Ntile is NOT supported by MSSQL 2000.
  • Notice that ntile does an approximation of how to break the data in equal fixed size buckets.
  • Ntile break some groups in 3s and some in 4s, but it's random. It's not fixed size!