DEV Community

Cover image for SQL: ROW_NUMBER, RANK and DENSE_RANK
Anwar
Anwar

Posted on

3 1 1 1 1

SQL: ROW_NUMBER, RANK and DENSE_RANK

Imagine you're tasked with building a ranking system for a gaming platform. How do you handle ties in scores? Take a look at the below image, where players on rank 3 have the same scores.

Score ties

What if you want to identify and remove duplicate entries from a user database while keeping only the latest record? Or perhaps you need to paginate millions of rows in a web application efficiently.

This is where ROW_NUMBER, RANK, and DENSE_RANK step in as your go-to SQL window functions, turning daunting problems into manageable queries.

They assign ranks or numbers to rows within a result set. These functions are often paired with ORDER BY and PARTITION BY clauses.

  • ORDER BY defines how rows are ordered for ranking.
  • PARTITION BY divides rows into groups (optional). Ranking restarts for each group.

Ready to uncover how these SQL superheroes can transform your data operations? Let’s dive in!

Sample data

SELECT * FROM Employees;
Enter fullscreen mode Exit fullscreen mode
ID Department EmployeeName Salary
1 HR Alice 9000
2 HR Bob 8500
3 HR Eve 8500
4 HR Tom 8000
5 IT Charlie 9500
6 IT David 8900
7 IT Emma 8900
8 IT Frank 8700

Query Using ROW_NUMBER, RANK, and DENSE_RANK

  • Let's write a query to rank employees within each department based on their salaries in descending order
SELECT 
    Department,
    EmployeeName,
    Salary,
    ROW_NUMBER() 
      OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
    RANK() 
     OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() 
     OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank
FROM Employees;
Enter fullscreen mode Exit fullscreen mode
Department EmployeeName Salary RowNum Rank DenseRank
HR Alice 9000 1 1 1
HR Bob 8500 2 2 2
HR Eve 8500 3 2 2
HR Tom 8000 4 4 3
IT Charlie 9500 1 1 1
IT David 8900 2 2 2
IT Emma 8900 3 2 2
IT Frank 8700 4 4 3

Key Differences

Function Unique Sequence Skips Ranks After Ties Continuous Ranks
ROW_NUMBER Yes N/A N/A
RANK No Yes No
DENSE_RANK No No Yes

Explanation

  • ROW_NUMBER generates a unique sequence for every row, ignoring ties.

  • RANK assigns the same rank for ties but skips the next ranks.

  • DENSE_RANK assigns the same rank for ties without skipping subsequent ranks.

Let me know if you’d like any other adjustments! πŸš€

Summary

The versatility of ROW_NUMBER, RANK, and DENSE_RANK makes them indispensable for scenarios like pagination, deduplication, pattern analysis, prioritization, and much more. Let me know if you'd like more examples or further clarification on any of these use cases! 😊

Sample Script: Creating and Populating the Employees Table

Table Creation

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Department VARCHAR(50),
    EmployeeName VARCHAR(50),
    Salary INT
);
Enter fullscreen mode Exit fullscreen mode

Inserting Sample Data

INSERT INTO Employees (EmployeeID, Department, EmployeeName, Salary) VALUES
(1, 'HR', 'Alice', 9000),
(2, 'HR', 'Bob', 8500),
(3, 'HR', 'Eve', 8500),
(4, 'HR', 'Tom', 8000),
(5, 'IT', 'Charlie', 9500),
(6, 'IT', 'David', 8900),
(7, 'IT', 'Emma', 8900),
(8, 'IT', 'Frank', 8700);
Enter fullscreen mode Exit fullscreen mode

References

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay