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.

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;

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;

Key Differences

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
);

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);

References

• CS2 leaderboard
• Stackoverflow