loading..
Русский    English
09:19

NTILE function

The task. Distribute paint cans (balloons) among 3 groups. The groups are filling in order of increasing of v_id.

This task can be solved with aid of ranking function NTILE which is available in SQL Server since 2008 version.

NTILE function returns the number of group to which the row of result set is to be assigned.

Console
Execute
  1. SELECT *, NTILE(3) OVER(ORDER BY v_id) gr FROM utv ORDER BY v_id;

NTILE function has a parameter which represents group number. The more that could be said you know yet by now. :-)

If we'll want to distribute the balloons of each color separately, we could use optional PARTITION BY element in the OVER clause, in the same manner as for other ranking functions.

Console
Execute
  1. SELECT *, NTILE(3) OVER(PARTITION BY v_color ORDER BY v_id) gr
  2. FROM utv ORDER BY v_color, v_id;

Pay attention to blue color groups (B). The two first groups include 6 balloons whereas the third one includes only 5.
In the case where number of rows can't be distributed equally, NTILE function puts in into the last groups by one row less.

At last, if argument of the NTILE function will be greater than number of rows, such a number of groups will be formed that equals to the number of rows, and each group will include only one row.

Suggested exercises: 130

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100