Difference Between Row_Number, Rank And Dense_Rank

Hello kiran,

You can find my article with detailed example which will help you out to understand,

https://www.c-sharpcorner.com/blogs/difference-between-rownumber-rank-denserank-in-sql-server

Hi Kiran,

• ROW_NUMBER: It just returns the sequence numbers starts from 1.
• RANK: Returns the rank of each record in the current result set.
• DENSE_RANK: It is same as Rank() function. But it returns without gaps in ranking.

 

Rank and Dense_Rank are similar to Row_Number, but when there are ties, they will give the same value to the tied values. rank will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas Dense_Rank will never give any gaps. See below example.

 

CREATE TABLE [Emp] (
    [EmpId] INT NOT NULL IDENTITY(1, 1),
    [Name] VARCHAR(255) NULL,
    [DeptId] INT NULL,
    [Salary] INT NULL,
    PRIMARY KEY ([EmpID])
);
GO
TRUNCATE TABLE [Emp]
INSERT INTO [Emp] ([Name], DeptId, Salary)
VALUES
  ('Britanni',3,1000),
  ('Julian',2,2000),
  ('Dolan',3,3000),
  ('Jonah',4,4000),
  ('Fulton',2,5000),
  ('Scarlett',4,1000),
  ('Bernard',2,2000),
  ('Chancellor',1,3000),
  ('Dalton',4,4500),
  ('Len',1,5000),
  ('Bree',4,1000),
  ('Cooper',1,2000),
  ('Nora',4,3000),
  ('Gareth',3,4000),
  ('Beau',2,5000); 

SQL Query

SELECT *,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY Salary) AS RowNumber,
      RANK() OVER(PARTITION BY DeptId ORDER BY Salary) AS [Rank],
DENSE_RANK() OVER(PARTITION BY DeptId ORDER BY Salary) AS [DenseRank]
FROM [dbo].[Emp]   

In SQL, ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions used to assign unique values to rows within a partition of a result set. However, they differ in how they handle ties (duplicate values) and the numbering sequence. Here's a breakdown of their differences:

ROW_NUMBER():
    Assigns a unique sequential integer to each row within a partition, starting from 1.
    Handling Ties: Does not handle ties. Even if two rows have the same values, they will still receive different row numbers.
    Use Case: Useful when you need a unique identifier for each row, regardless of duplicate values.

SELECT 
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

RANK():
    Assigns a unique rank to each row within a partition, but leaves gaps in the ranking sequence when there are ties.
    Handling Ties: Rows with the same values receive the same rank, and the next rank(s) are skipped.
    Use Case: Useful when you want to rank rows but account for ties, with gaps in the ranking sequence.

SELECT 
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

DENSE_RANK():
    Assigns a unique rank to each row within a partition, but does not leave gaps in the ranking sequence when there are ties.
    Handling Ties: Rows with the same values receive the same rank, and the next rank is not skipped.
    Use Case: Useful when you want to rank rows without gaps in the ranking sequence, even when there are ties.

SELECT 
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Please refer below article.

https://www.c-sharpcorner.com/UploadFile/85ed7a/difference-between-rownumber-rank-and-denserank-in/