SQL: NTILE() function

You may have read the posts on ROW_NUMBER(), RANK() and DENSE_RANK() functions. There is one more ranking function - NTILE().

This function will return a value for each row that will tell you which "group" the row belongs to. You will specify a value to the NTILE function (e.g. 4) - this tells the function that the group numbers are 1, 2, 3 and 4 (starting at one up to and including the value specified). The rows will be ordered and assigned to the groups depending on the position within the group.

This SQL

SELECT 
   ROW_NUMBER() OVER ( ORDER BY MONTH(OrderDate)) AS RowNum,
   NTILE(4) OVER (ORDER BY MONTH(OrderDate)) AS TileNum,
   MONTH(OrderDate) AS MonthOfOrder,
   CustomerID, 
   OrderID
FROM Orders
WHERE CustomerID IN ('ALFKI','ANATR')
ORDER BY MONTH(OrderDate)

Producing this output

RowNum               TileNum              MonthOfOrder CustomerID OrderID
-------------------- -------------------- ------------ ---------- -----------
1                    1                    1            ALFKI      10835
2                    1                    3            ANATR      10926
3                    1                    3            ALFKI      10952
4                    2                    4            ALFKI      11011
5                    2                    8            ANATR      10625
6                    2                    8            ALFKI      10643
7                    3                    9            ANATR      10308
8                    3                    10           ALFKI      10692
9                    4                    10           ALFKI      10702
10                   4                    11           ANATR      10759

We have ten rows and 4 partitions - partitions 1, 2 and 3 have three rows assigned to that group and the partition 4 only has two rows.

You can also use PARTITION within the OVER clause (as you can do for the other ranking functions).