ROW_NUMBER(),RANK() AND DENSE_RANK()

Introduction:

There are three SQL functions that allow us to assign the row numbers to rows in query output based on our requirement. These functions are ROW_NUMBER(),RANK() and DENSE_RANK()

Syntax:

ROW_NUMBER() OVER (Partition by column name Order by column name)

RANK() OVER (Partition by column name Order by column name)

DENSE_RANK() OVER (Partition by column name Order by column name)

Row_Number (): This function assign a row number based on the order by. Each row will have different row number RANK (): This function assigns rank based on an order by clause. Same values will get same row number
DENSE_RANK (): This function assigns rank based on an order by clause. Same values will get same row number, but there is a small The difference between RANK () and DENSE_RANK () which we will discuss later in this post.

Demo:

Lets create the table first

CREATE TABLE SalesByPerson
(EmpID int Identity,
Emp_Name varchar(20),
Sales MONEY,
Region Varchar(10))
GO
INSERT INTO SalesByPerson Values('Sara',200,'US'),('Nadeem',150,'Europe'),('Adam',250,'US'),

('Ryan',350,'Canada'),('James',100,'Europe'),('Ajay',150,'Canada'),('Krish',160,'Canada'),
('Carlos',100,'US'),('Dave',200,'US'),('Shem',350,'Europe'),('Shane',200,'Canada'),
('Nikita',150,'US')

We are not using partition by for now.

Select Emp_Name,Sales,Row_Number() OVER (Order by Sales desc) as RowNumber from SalesByPerson


Row_Number () has assigned a different row numbers to each row.


Select Emp_Name,Sales,RANK() OVER (Order by Sales desc) as [RANK] from SalesByPerson
Select Emp_Name,Sales,DENSE_RANK() OVER (Order by Sales desc) as [DENSERANK] from SalesByPerson

RANK () Same sales values has been assigned same rank and different Ranks to different sales values But if the same number is assigned to two rows, then next number is skipped.
Ex: Rank 1 is assigned to two rows so rank 2 was skipped
DENSE_RANK (): Same sales value has been assigned with same rank and different Ranks to different sales values and no rank is skipped.

Use of Partition by:

Partition by reset the row number based on the column name. Lets check how does it work.

Select Emp_Name,Sales,Region,Row_Number() OVER (Partition by Region Order by Sales desc) as RowNumber from SalesByPerson


Select Emp_Name,Sales,Region,RANK() OVER (Partition by Region Order by Sales desc) as [RANK] from SalesByPerson


Select Emp_Name,Sales,Region,DENSE_RANK() OVER (Partition by Region Order by Sales desc) as [DENSERANK] from SalesByPerson

As you can see in output that row numbers are reset for each region.

Happy Learning….!!!

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

1 thought on “ROW_NUMBER(),RANK() AND DENSE_RANK()

  1. I think that is one of the such a lot significant information for me. And i am glad studying your article. But want to remark on few general things, The site style is wonderful, the articles is truly nice : D. Excellent job, cheers

Leave a Reply

Your email address will not be published.