SQL NTILE FUNCTION 2

SQL NTILE Function

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

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 it has to assign for each partition. For instance, If we specify 2, the 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 NTILE Function example, we are going to use the below Table data.

Customer Table

SQL NTILE Function Example

The 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 available 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]
NTILE 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 sort the data in a descending order using their [yearly income]

We used NTILE(2) Function with Partition by clause in the following statement. 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), it has been given the same rank to them. Because NTILE(2) can assign only 2 two ranks for a separation.

SQL NTILE Function Example 2

In this 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 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

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

SQL Server NTILE Function without Partition By Clause

What will happen if we miss the Partition By Clause in 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 the ten records as a single partition. Next, It will assign two rank numbers to that ten records.

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

Comments are closed.