SQL DENSE_RANK() Tutorial 🎯

Welcome to the SQL DENSE_RANK() tutorial! Today, we're going to dive deep into understanding how to use DENSE_RANK() function in SQL. This function is a powerful tool in SQL that helps you order and number rows within a result set. Let's get started! 📝

What is DENSE_RANK()?

DENSE_RANK() is a ranking function in SQL that assigns consecutive integers to each row in a query result set, without any gaps. This function is useful when you want to assign ranks to rows without skipping any values, even if there are ties in the data. 💡

Syntax of DENSE_RANK()

The syntax for using DENSE_RANK() is as follows:

sql

DENSE_RANK() OVER (ORDER BY column1 [ASC | DESC], ...)

Here, column1 is the column based on which we want to sort the data, and ASC or DESC specifies the order of the sorting (ascending or descending, respectively).

Example 1: Simple DENSE_RANK() Usage

Let's consider a simple example of a table students with columns name and score. We want to rank the students based on their scores using DENSE_RANK().

sql

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

INSERT INTO students (name, score)
VALUES
  ('Alice', 95),
  ('Bob', 90),
  ('Charlie', 85),
  ('David', 90),
  ('Eve', 80);

Now, let's rank the students using DENSE_RANK().

sql

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

This query will output:

| name   | score | rank |
|--------|-------|------|
| Alice  |   95  |    1 |
| Bob    |   90  |    2 |
| David  |   90  |    2 |
| Charlie|   85  |    3 |
| Eve    |   80  |    4 |

In this example, we have two students with a score of 90. The DENSE_RANK() function assigns the same rank (2) to both of them, as it does not skip any values in the ranking process.

Example 2: Real-world Scenario with Ties

Let's consider a table sales with columns sale_id, product_id, and quantity. We want to rank the sales based on the quantity of each product.

sql

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, product_id, quantity)
VALUES
  (1, 1, 10),
  (2, 1, 8),
  (3, 2, 5),
  (4, 2, 3),
  (5, 3, 12),
  (6, 3, 7);

Now, let's rank the sales based on the quantity of each product using DENSE_RANK().

sql

SELECT product_id, quantity, DENSE_RANK() OVER (PARTITION BY product_id ORDER BY quantity DESC) AS rank
FROM sales;

This query will output:

| product_id | quantity | rank |
|------------|----------|------|
|          1 |       10 |    1 |
|          1 |        8 |    2 |
|          2 |        5 |    1 |
|          2 |        3 |    2 |
|          3 |       12 |    1 |
|          3 |        7 |    2 |

In this example, we have used the PARTITION BY clause to rank the sales separately for each product. The DENSE_RANK() function assigns ranks within each product, without skipping any values.

Quiz 📝

That's it for today! In the next lesson, we'll explore the SQL ROW_NUMBER() function, which is another powerful ranking function in SQL. Until then, keep learning, and happy coding! 💡🎯