Search notes:

SQL: NTILE

ntile(n) over (order by val) allows to partition an SQL result set into n groups of (approximatively) equal numbers of records.

The returned value of ntile is an integer between 1 and n which indicates the group number into which a record belongs.

The group number that the record falls into is determined by the order by val clause: the lower val is, the lower the assigned group is.

I try to demonstrate that behaviour with the following simple example.

First, I need to have a table …

create table tq84_n (
   item   varchar(20 ) not null,
   val    decimal(5,2) not null
);

Github repository about-SQL, path: /select/analytic/ntile/create-table.sql

… into which I insert some data:

insert into tq84_n values ('Orange'    , 5.63);
insert into tq84_n values ('Papaya'    , 0.98);
insert into tq84_n values ('Banana'    , 1.21);
insert into tq84_n values ('Pineapple' , 2.14);
insert into tq84_n values ('Mango'     , 3.84);
insert into tq84_n values ('Guava'     , 5.08);
insert into tq84_n values ('Mosumbi'   , 4.78);
insert into tq84_n values ('Jack Fruit', 8.32);

Github repository about-SQL, path: /select/analytic/ntile/insert-values.sql

I now want to create the inserted fruits into three groups: the cheapest (group 1), the medium priced (group 2) and the expensive ones (group 3).

Also, within a group, I want to order the records by the fruit (as opposed to their value):

select
   ntile(3) over (order by val) group_no,
   item,
   val
from
   tq84_n
order by
   1,
   item;

Github repository about-SQL, path: /select/analytic/ntile/select-ntile.sql

The query returns

group_no item        val     
-------- ----------- ------- 
1        Banana      1.21    
1        Papaya      0.98    
1        Pineapple   2.14    
2        Guava       5.08    
2        Mango       3.84    
2        Mosumbi     4.78    
3        Jack Fruit  8.32    
3        Orange      5.63

SQL: NTILE

See also