SQL RANK() Tutorial 🎯

Welcome to our comprehensive SQL RANK() tutorial! Today, we'll dive into understanding the powerful SQL RANK() function and how it can help you sort and analyze your data more effectively.

What is SQL RANK()? 📝

The SQL RANK() function is a powerful tool that assigns a unique rank to each row in a result set based on the order of the rows. This function is useful when you want to assign ranks to a group of records, such as determining the rank of a student in a class or the rank of a product in sales.

Why Use SQL RANK()? 💡

Simplifies Data Analysis: The RANK() function allows you to quickly analyze the order of your data without having to write complex queries.
Handles Ties: If there are ties in your data, the RANK() function will assign the same rank to those tied rows.
Real-World Applications: The RANK() function is useful in various scenarios such as leaderboards, product sales rankings, and more.

Syntax and Examples 💻

The basic syntax of the SQL RANK() function is:

sql

RANK() OVER (ORDER BY column_name)

Let's consider an example where we have a table named students:

sql

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  score INT
);

INSERT INTO students (id, name, score)
VALUES (1, 'John', 95), (2, 'Mike', 88), (3, 'Alice', 90), (4, 'Bob', 92), (5, 'Emily', 90);

To find the rank of each student based on their score, we can use the RANK() function as follows:

sql

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

This will return:

id | name | score | rank
--:|------|------:|-----:
 1 | John |     95 |  1
 5 | Emily |     90 |  2
 4 | Bob  |     92 |  3
 3 | Alice |     90 |  3
 2 | Mike |     88 |  5

As you can see, the RANK() function correctly assigns ranks based on the scores.

Advanced Example 💡

In some cases, you might need to handle ties in the data. To do this, you can use the DENSE_RANK() function. This function assigns consecutive ranks to all the rows without gaps. Let's modify our previous example to include this:

sql

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

This will return:

id | name | score | rank
--:|------|------:|-----:
 1 | John |     95 |  1
 5 | Emily |     90 |  2
 4 | Bob  |     92 |  3
 3 | Alice |     90 |  3
 2 | Mike |     88 |  5

As you can see, the students with a score of 90 (Alice and Emily) both have a rank of 3, demonstrating the handling of ties with the DENSE_RANK() function.

Happy learning! 📝💡🎯