SQL NTILE Function

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:

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, 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 havent specified the Partition By Clause then, NTILE Function will consider all the records as 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 2014 with example. For this, We are going to use the below shown data

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

OUTPUT

ANALYSIS

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

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

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

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

SQL NTILE Function Example 2

In this example we will show you, What will happen if we assign different value at Integer_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).

T-SQL CODE

Above query will assign three rank number per partition.

OUTPUT

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. For instance, The following SQL Query will use the above example query without Partition by clause.

T-SQL CODE

OUTPUT

ANALYSIS

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

In the below statement we used NTILE(2) Function without Partition by clause. Here integer value is 2, It means NTILE Function will assign two rank number for each individual partition.

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

SQL NTILE Function without Partition By Clause 2

In this example we will show you, What will happen if we assign different value at Integer_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).

T-SQL CODE

OUTPUT

Here we haven’t used any partition, NTILE Function will consider all the 10 records as 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