loading..
Русский    English
09:38
go

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.

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
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.