SQL NTILE FUNCTION 2

SQL NTILE Function

The SQL NTILE Function is one of the 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: 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, SQL 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 specified 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 Rank Table data

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 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]
SQL NTILE FUNCTION 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]

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

Although the yearly income is not identical 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 separation.

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

Above query will assign three rank number 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 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.

Here integer value is 2. It means the SQL 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]
SQL NTILE FUNCTION 3

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 2 to 3.

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(3) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]
SQL NTILE Function 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.