Русский    English

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  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server since 2008 version.

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

  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.

  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
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 CONSTRAINT 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 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100