SQL NTILE Function

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

SQL NTILE Function Syntax

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

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

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

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

For this SQL Server NTILE Function example, we are going to use the below Table data.

Customer Table

SQL NTILE Function Example

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

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

The below PARTITION BY [Occupation] 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.

This Order By sorting the data in descending order using their [yearly income].

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

Although the yearly income is not identical for 6 and 7 records (90000 and 80000), they have been given the same rank because NTILE(2) can assign only 2 two ranks for a separation.

SQL NTILE Function Example 2

In this NTILE function 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 number from 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]

The above query will assign three rank numbers per occupation.

SQL NTILE FUNCTION 2

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 an occupation.

SQL Server NTILE Function without Partition By Clause

What will happen if we miss the Partition By Clause in SQL Server NTILE Function? For instance, the following query will use the above example query without Partition by clause.

Here integer value is 2. It means the NTILE Function will assign two rank numbers for each portion.

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

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

NTILE without Partition By Clause 2

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

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(3) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]
SQL NTILE Function without Partition By Clause 4

Here we haven’t used any partition clause. It means this Function will consider all 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.

Categories SQL