Tutorial Gateway

SQL NTILE Function

by

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

SQL NTILE Function Syntax

The basic 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 specified the Partition By Clause then, 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: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.

In this article, we will show you, How to write NTILE Function in SQL Server with an example. For this, 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 present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.

T-SQL CODE

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

Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

Below statement will sort the partitioned data in the descending order using their [yearly income]

ORDER BY [YearlyIncome] DESC

In the below 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 individual partition (4 partitions).

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

Although the yearly income of is not same for 6 and 7 records (90000 and 80000), It 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 will show you, What will happen if we assign a different value at the Integer_Value position.

The following example Query will use the above example query but we changed the NTILE (2) to NTILE(3).

T-SQL CODE

USE [SQL Server Tutorials]
GO
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

Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because NTILE(3) will assign 3 ranks for a partition.

SQL NTILE Function without Partition By Clause 1

In this example we will show you, 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.

T-SQL CODE

USE [SQL Server Tutorials]
GO

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

OUTPUT
SQL NTILE FUNCTION 3

ANALYSIS

Below statement will sort the partitioned data in the descending order using their [yearly income]

ORDER BY [YearlyIncome] DESC

In the below statement 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 individual partition.

Since we haven’t used any partition, SQL NTILE Function will consider all the 10 records as a single partition. Next, It will assign 2 rank number to that 10 records.

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

SQL NTILE without Partition By Clause 2

In this SQL NTILE function example, we will show you, What will happen if we assign a different value at the Integer_Value position.

For instance, The following Query will use the above example query but we changed the NTILE (2) to NTILE(3).

T-SQL CODE

USE [SQL Server Tutorials]
GO

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. It means, SQL NTILE Function will consider all the 10 records as a single partition. Next, It will assign 3 rank number to that 10 records because we have given Integer_Value as 3.

Thank You for Visiting Our Blog

Placed Under: SQL