Oracle Quick Tip: Divide a result set into groups using NTILE

The NTILE analytic function divides a result set into a specified number of groups or buckets.

The following SQL takes a generated result set and, using NTILE, adds a column containing three distinct values with an equal number of rows for each value.

SELECT id, val, ntile(3) OVER(ORDER BY id) nt
FROM (
  SELECT rownum id, 'xyz' || rownum val
  FROM dual
  CONNECT BY LEVEL <=12
)
ORDER BY id;

ID       VAL          NT
-------- ------------ ---------
1        xyz1         1
2        xyz2         1
3        xyz3         1
4        xyz4         1
5        xyz5         2
6        xyz6         2
7        xyz7         2
8        xyz8         2
9        xyz9         3
10       xyz10        3
11       xyz11        3
12       xyz12        3

12 rows selected