Tutorial Gateway

SQL NTILE Function

by

The SQL NTILE Function is one of the SQL ranking functions. This NTILE function will assign the rank number to each record present in a partition. The syntax of the SQL Server NTILE function is:

SELECT NTILE(Interger_Value) OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]

Integer_Value: NTILE Function will use this integer value to decide the number of ranks it has to assign for each partition. For instance, If we specify 2, SQL NTILE Function will assign 2 rank numbers for each partition.

Partition_By_Clause: This will divide the records selected by the SQL SELECT Statement into partitions.

  • If you specified the Partition By Clause then, NTILE Function will assign the rank number to each partition.
  • If you haven’t, SQL NTILE Function will consider all the records as a single partition. So, it will assign the rank numbers from top to bottom.

Order_By_Clause: Sort the Partitioned data in a specific order. Please refer to SQL Order By Clause for a greater understanding.

For this NTILE Function in SQL Server example, we are going to use the below-shown data

SQL NTILE FUNCTION

SQL NTILE Function Example 1

The SQL NTILE Function allows you to assign the rank number to each record present in a partition. In this example, we will show you how to rank the partitioned records available in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.

USE [SQL Server Tutorials]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,NTILE(2) OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [NTILE NUMBER]
  FROM [Customers]

OUTPUT

SQL NTILE FUNCTION 1

ANALYSIS

The below statement uses the Occupation column to separate the selected data into a partition. From the above image, you can observe that we got four partitions

PARTITION BY [Occupation]

This Order By statement sort the partitioned data in a descending order using their [yearly income]

ORDER BY [YearlyIncome] DESC

In the following statement, we used NTILE(2) Function with Partition by clause. Here integer value is 2. It means SQL NTILE Function will assign two rank number for each partition (4 partitions).

NTILE(2) OVER (
               PARTITION BY [Occupation] 
               ORDER BY [YearlyIncome] DESC
             ) AS [NTILE NUMBER]

Although the yearly income is not identical for 6 and 7 records (90000 and 80000), NTILE has given the same rank to them. Because NTILE(2) can assign only 2 two ranks for a partition.

SQL NTILE Function Example 2

In this SQL ntile example, we show you what will happen if we assign a different value at the Integer_Valueposition.

The following example uses the above example query, but we changed the NTILE (2) to NTILE(3).

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,NTILE(3) OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [NTILE NUMBER]
  FROM [Customers]

Above query will assign three rank number per partition.

OUTPUT

SQL NTILE FUNCTION 2

Here, the yearly income is the same for 3 and 4 records (80000). However, it has given different ranks to them because NTILE(3) will assign three ranks for a partition.

SQL NTILE Function without Partition By Clause 1

What will happen if we miss the Partition By Clause in NTILE Function in SQL Server?

For instance, the following SQL NTILE Query will use the above example query without Partition by clause.

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(2) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]

OUTPUT

SQL NTILE FUNCTION 3

ANALYSIS

We used NTILE(2) Function without Partition by clause. Here integer value is 2. It means SQL NTILE Function will assign two rank number for each partition.

Since we haven’t used any partition, NTILE Function will consider all the ten records as a single partition. Next, It will assign two rank numbers to that ten records.

NTILE(2) OVER (
               PARTITION BY [Occupation] 
               ORDER BY [YearlyIncome] DESC
             ) AS [NTILE NUMBER]

NTILE without Partition By Clause 2

In this SQL NTILE function example, we assign a different value at the Integer_Value position. For instance, here, we changed the NTILE (2) to NTILE(3).

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(3) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]

OUTPUT

SQL NTILE Function 4

Here we haven’t used any partition clause. It means NTILE Function will consider all the 10 records as a single partition. Next, It will assign 3 rank numbers to that 10 records because we have given Integer_Value as 3.

Placed Under: SQL