Ranking Window Function

Master the Ranking Window Function in SQL to efficiently rank and analyze your data. Learn how to use this powerful tool to optimize query performance and gain valuable insights from your datasets.

Ranking Window Function in SQL

The Ranking window functions are used to assign ranks to rows based on the ordering of a specific column. These functions are incredibly useful for tasks like ranking players in a game, employees based on performance, or students based on their grades. There are three common ranking window functions in SQL:

1. ROW_NUMBER(): Assigns a unique sequential number to each row within a partition of a result set, starting at 1.
2. RANK(): Assigns a rank to each row, but when there are ties (identical values), the same rank is given to those rows, and the next rank is skipped.
3. DENSE_RANK(): Similar to RANK(), but without skipping ranks when there are ties (i.e., no gaps in ranking).

Key Differences Between Ranking Functions

• ROW_NUMBER() assigns a unique number to each row, even if some values are the same. This is useful when you need a strict ranking order.
• RANK() handles ties by giving the same rank to rows with the same values, but it skips the subsequent rank(s). For example, if two people are ranked 1st, the next rank will be 3rd.
• DENSE_RANK() also assigns the same rank to tied values but doesn’t skip the next rank. For example, if two people are ranked 1st, the next rank will be 2nd.

Syntax for Ranking Functions

sqlCopy code-- ROW_NUMBER()
ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)

-- RANK()
RANK() OVER (PARTITION BY column ORDER BY column)

-- DENSE_RANK()
DENSE_RANK() OVER (PARTITION BY column ORDER BY column)

• PARTITION BY (optional): Used to divide the data into groups (e.g., departments, product categories).
• ORDER BY: Specifies the order in which the ranking should be assigned (e.g., descending order of scores).

Examples of Ranking Functions

1. ROW_NUMBER() Example

Let’s assume you have the following table of students with their scores:

If you use ROW_NUMBER(), each student will get a unique rank based on their score, even if the scores are the same.

sqlCopy codeSELECT 
    Student, 
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

Notice that despite Alice and Charlie both having the highest score (90), they are assigned different row numbers.

2. RANK() Example

Now, let’s use RANK() for the same data:

sqlCopy codeSELECT 
    Student, 
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

Here, Alice and Charlie both share rank 1. However, notice that after the tie for rank 1, the next rank is 3 (it skips 2).

3. DENSE_RANK() Example

Using DENSE_RANK():

SELECT 
    Student, 
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

With DENSE_RANK(), Alice and Charlie still share rank 1, but after that, the next rank is 2 (no skipping).

Use Cases for Ranking Functions

1. Leaderboard or Top N Ranking If you want to get the top N students, employees, or players based on performance, you can use ranking functions.Example: Getting the top 3 students based on their scores:sqlCopy codeSELECT Student, Score, RANK() OVER (ORDER BY Score DESC) AS Rank FROM Students WHERE RANK() <= 3;
2. Assigning Tiers or Groups You might need to divide people into groups or tiers based on their scores. For instance, rank employees into tiers based on sales.
3. Handling Ties in Competitions You can use RANK() or DENSE_RANK() to rank participants who have identical scores, with or without skipping ranks.
4. Ordering Records Without Changing the Data Ranking allows you to assign ranks to records based on certain criteria (e.g., sales, performance) while still retaining the original row data.

Common SQL Ranking Questions

1. Rank students by score: Problem: Rank students in descending order of their score.sqlCopy codeSELECT Student, Score, RANK() OVER (ORDER BY Score DESC) AS Rank FROM Students;
2. Top N performers: Problem: Get the top 5 employees by sales amount.sqlCopy codeSELECT Employee, Sales, RANK() OVER (ORDER BY Sales DESC) AS Rank FROM Employees WHERE Rank <= 5;
3. Rank products by sales: Problem: Rank products based on the total sales amount and display the rank alongside each product.sqlCopy codeSELECT ProductName, SUM(SalesAmount) AS TotalSales, RANK() OVER (ORDER BY SUM(SalesAmount) DESC) AS Rank FROM Sales GROUP BY ProductName;
4. Assign groups to employees based on salary ranges: Problem: Rank employees within salary ranges.sqlCopy codeSELECT EmployeeName, Salary, DENSE_RANK() OVER (PARTITION BY Salary ORDER BY Salary) AS SalaryGroup FROM Employees;

SQL