Difference Between ROW NUMBER vs RANK vs DENSE RANK in SQL

In SQL ROW NUMBER vs RANK vs DENSE RANK is the most common Interview Question. Apart from this, they are also very important for SQL Query implementation for getting the Nth highest or lowest salary or checking duplicate or deleting duplicate or applying any aggregate function.

In this article, we will walk through the main difference between ROW NUMBER, RANK, and DENSE RANK in SQL.

Please visit our YouTube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Today's Interview Questions

Q01. What is the difference between ROW NUMBER, RANK, and DENSE RANK in SQL?

Q02. What is the use of the ROW_NUMBER() function in SQL?

Q03. What is the use of the RANK() function in SQL?

Q04. What is the use of the DENSE_RANK() function in SQL?

Q05. What is the use of the PARTITION BY clause in SQL?

Q06. Can you use ROW_NUMBER, RANK, or DENSE_RANK function without the ORDER BY clause?

Q07. What is the difference between ROW_NUMBER() and RANK() function in SQL?

Q08. What is the difference between the RANK() and DENSE_RANK() function in SQL?

Q09. Can you give any real use of the ROW_NUMBER() function in SQL?

Q10. Can you give any real use of the RANK() function in SQL?

Q11. Can you give any real use of the DENSE_RANK() function in SQL?

Introduction

All three functions are used to get the ascending integer sequential numbers in SQL. I have created the EmployeePayroll table for this demo. We will use this table for all the examples in this article.

RowNumber vs Rank vs DenseRank in SQL

All the three functions ROW_NUMBER, RANK, and DENSE_RANK have the following similarities:

All are used to get the increasing integer values.
The return type of all three functions is BIGINT.
All three SQL functions are Nondeterministic.
All three functions use the OVER Clause.
All the there functions use the ORDER BY Clause.
All the three functions may use the PARTITION BY Clause.

Now before going on the difference between ROW NUMBER, RANK, and DENSE RANK, let's see some terms related to them.

Deterministic SQL Function

In SQL the Deterministic Functions always return the same result any time they are called with a specific set of input values and given the same state of the database.

For example COALESCE, DATALENGTH, ISNULL, NULLIF, SQUARE, DATEADD, DATEDIFF, POWER, etc all are Deterministic SQL Functions.

Nondeterministic SQL Function

In SQL the Nondeterministic Functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

For example, GETDATE, CURRENT_TIMESTAMP, ROW_NUMBER, RAND, RANK, DENSE_RANK, etc are Nondeterministic SQL Functions.

OVER Clause in SQL

In SQL the OVER clause determines the partitioning and ordering of a rowset before the associated window function is applied to it. The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.

For example, you can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

PARTITION BY Clause in SQL

In SQL the PARTITION BY clause divides the query result set into partitions produced by the FROM clause.

ORDER BY Clause in SQL

The ORDER BY clause is used (here in this case) to determine the sequence in which the rows are assigned their unique ROW_NUMBER, RANK, or DENSE_RANK within a specified partition. The ORDER BY clause is mandatory for all three functions whether it is RowNumber, Rank, or DenseRank.

SQL ROW NUMBER Function

The Row Number Function in SQL is a nondeterministic function that numbers the output of a result set starting with one. ROW_NUMBER is a temporary value calculated when the query is run.

The Row Number function numbers all the rows sequentially without any gap or duplicate unlike Rank and Dense Rank. The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. The ORDER BY clause is mandatory here.

We can also use the PARTITION BY clause to divide the result set produced by the FROM clause into partitions. In that case, the RowNumber returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

If the PARTITION BY clause is not specified the ROW_NUMBER function treats all rows of the query result set as a single group, and the sequence number is generated accordingly. There is no guarantee that the rows returned by a query using ROW_NUMBER will be ordered exactly the same with each execution.

SQL RANK Function

The RANK Function in SQL is a nondeterministic function. It returns the rank of each row within the partition of a result set. Rank is a temporary value calculated when the query is run.

The Rank of a row is one plus the number of ranks that come before the row in question. If two or more rows tie for a rank, each tied row receives the same rank. Therefore, the RANK function does not always return consecutive integers.

The Row Number function numbers all the rows sequentially without any gap or duplicate but the RANK function generates the same Rank for two or more rows that are tied (duplicate). It also skips the number for the position of each duplicate row.

We can also use the PARTITION BY clause to divide the result set produced by the FROM clause into partitions, and then we can get the Rank for each partition.

SQL DENSE RANK Function

The DENSE RANK Function in SQL is also a nondeterministic function. It also returns the rank of each row within the partition of a result set. Dense Rank is also a temporary value calculated when the query is run.

The only difference between RANK vs DENSE RANK is that DENSE RANK returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive rank values.

ROW NUMBER vs RANK vs DENSE RANK (Without Duplicate Records)

All the ROW NUMBER, RANK, and DENSE RANK functions generate the same number if there are no duplicate records, on which we have used the Order By Clause within the OVER clause.

If you see the result of ROW_NUMBER, RANK, and DENSE_RANK all are the same.

When we do not apply the PARTITION BY Clause, these function treats all rows of the query result set as a single group.

The PARTITION BY clause Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER, RANK, or DENSE_RANK function or any other aggregate function is applied.

So if you see the same ROW_NUMBER, RANK, and DENSE_RANK, department-wise, every partition has a separate Row Number and Rank range.

ROW NUMBER vs RANK vs DENSE RANK (With Duplicate Records)

We have some duplicate salaries in the table as below:

As you can see we have duplicate salaries for HR and the Finance department. So let's see what is the result for ROW NUMBER, RANK, and DENSE RANK here.

row-number-vs-rank-vs-dense-rank-duplicate-rows

Here you can see that we have, all the values in a numeric sequential only for the ROW NUMBER function.

For RANK the values 2, 5, 8 are assigned to multiple rows at the same the numbers 3, 6 are missing in the Rank.

The DENSE RANK also has duplicate number assignments but there is no missing rank value (3, 6) like the RANK function.

Now let's use Department Wise Row Number, Rank, and Dense Rank for each employee:

Here you can see that the Row Number, Rank, and Dense Rank are generated separately for each department.

Live Demo

SQL Scripts

   
 

-- Create EmployeePayroll Table
CREATE TABLE EmployeePayroll
(
	[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](50) NOT NULL,
	[Department] [varchar](50) NULL,
	[Salary] numeric(18,2) NULL
);

-- INSERT data into EmployeePayroll Table
INSERT INTO EmployeePayroll(Name, Department, Salary)
VALUES ('Alex', 'HR',90000),
	   ('Thomas', 'Finance', 75000),
	   ('Peter','HR', 95000),
	   ('Anton','Finance', 80000),
	   ('Brien', 'Sales', 70000),
	   ('Maria','Finance', 85000);
	   
	   
-- ROW_NUMBER vs RANK vs DENSE RANK 
-- When No Duplicate Records

 SELECT 
		Id,
		Name,
		Department,
		Salary, 
		ROW_NUMBER()   OVER (ORDER BY Salary DESC) AS RowNumber, 
		RANK()	       OVER (ORDER BY Salary DESC) AS Rank,  
		DENSE_RANK ()  OVER (ORDER BY Salary DESC) AS DenseRank	
FROM EmployeePayroll;


-- ROW_NUMBER vs RANK vs DENSE RANK 
-- When No Duplicate Records (Department Wise)

 SELECT 
		Id,
		Name,
		Department,
		Salary, 
		ROW_NUMBER()   OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNumber, 
		RANK()	       OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,  
		DENSE_RANK ()  OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank	
FROM EmployeePayroll;


-- INSERT data into EmployeePayroll Table
INSERT INTO EmployeePayroll(Name, Department, Salary)
VALUES ('Sid', 'HR',90000),
	   ('Sadric', 'Finance', 75000),	  
	   ('Ansha', 'Sales', 60000);
	   
	   
-- ROW_NUMBER vs RANK vs DENSE RANK 
-- When Duplicate Records

 SELECT 
		Id,
		Name,
		Department,
		Salary, 
		ROW_NUMBER()   OVER (ORDER BY Salary DESC) AS RowNumber, 
		RANK()	       OVER (ORDER BY Salary DESC) AS Rank,  
		DENSE_RANK ()  OVER (ORDER BY Salary DESC) AS DenseRank	
FROM EmployeePayroll;
	   
	   
	   
-- ROW_NUMBER vs RANK vs DENSE RANK 
-- When Duplicate Records (Department Wise)

 SELECT 
		Id,
		Name,
		Department,
		Salary, 
		ROW_NUMBER()   OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNumber, 
		RANK()	       OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,  
		DENSE_RANK ()  OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank	
FROM EmployeePayroll;


-- Payroll Table With Duplicate Salary Records
 SELECT 
		Id,
		Name,
		Department,
		Salary		
FROM EmployeePayroll
ORDER BY Salary DESC;

Sunday, March 13, 2022

Difference Between ROW NUMBER vs RANK vs DENSE RANK in SQL