IURIX
Begin main content

DENSE_RANK()

The DENSE_RANK() ranking window function is similar to the RANK() function by generating a unique rank number for each distinct row within the partition according to a specified column value, starting at 1 for the first row in each partition, ranking the rows with equal values with the same rank number, except that it does not skip any rank, leaving no gaps between the ranks.

If we rewrite the previous ranking query to use the DENSE_RANK() ranking function:

The output of the query where the DENSE_RANK() ranking function is used

Again, modify the previous query by including the PARTITION BY clause to have more than one partition, as shown in the T-SQL query below:

SELECT *, DENSE_RANK() OVER(PARTITION BY Student_Score  ORDER BY Student_Score) AS RowNumberRank
FROM StudentScore

The ranking values will have no meaning, where all the rows will be ranked with the value 1, due to assigning the duplicate values to the same ranking value and resetting the rank starting id when processing a new partition, as shown below:

The ranking values will have no meaning, where all the rows will be ranked with the value 1, due to assigning the duplicate values to the same ranking value and resetting the rank starting id when processing a new partition

NTILE(N)

The NTILE(N) ranking window function is used to distribute the rows in the rows set into a specified number of groups, providing each row in the row set with a unique group number, starting with the number 1 that shows the group this row belongs to, where N is a positive number, which defines the number of groups you need to distribute the rows set into.

In other words, if you need to divide specific data rows of the table into 3 groups, based on particular column values, the NTILE(3) ranking window function will help you to achieve this easily.

The number of rows in each group can be calculated by dividing the number of rows into the required number of groups. If we modify the previous ranking query to use NTILE(4) ranking window function to rank seven table rows into four groups as the T-SQL query below:

SELECT *,  NTILE(4) OVER( ORDER BY Student_Score) AS NTILERank
FROM StudentScore

The number of rows should be (7/4=1.75) rows into each group. Using the NTILE() function, SQL Server Engine will assign 2 rows to the first three groups and one row to the last group, in order to have all the rows included in the groups, as shown in the result set below:

Using the NTILE() function, SQL Server Engine will assign 2 rows to the first three groups and one row to the last group, in order to have all the rows included in the groups

Modifying the previous query by including the PARTITION BY clause to have more than one partition, as shown in the T-SQL query below:

SELECT *, NTILE(4) OVER(PARTITION BY Student_Score  ORDER BY Student_Score) AS RowNumberRank
FROM StudentScore

The rows will be distributed into four groups on each partition. For example, the first two rows with Student_Score equal to 770 will be in the same partition, and will be distributed within the groups ranking each one with a unique number, as shown in the result set below:

The rows will be distributed into four groups on each partition. For example, the first two rows with Student_Score equal to 770 will be in the same partition, and will be distributed within the groups ranking each one with a unique number

12:37 PM, 15 Sep 2021 by Iuri Sampaio Permalink

Add comment