DENSE_RANK in SQL. How it works including examples.

DENSE_RANK in SQL. How it works including examples.

Overview of DENSE_RANK in SQL

The DENSE_RANK function in SQL is used to rank rows in a result set with no gaps in ranking values. Unlike the RANK function, which might skip some rank numbers (for example, 1, 2, 2, 4), the DENSE_RANK function will not skip rank numbers (for example, 1, 2, 2, 3). This function is particularly useful when you want to assign ranks to rows in a way that continuous numerical ranks are maintained.

Syntax

The syntax of the DENSE_RANK function is as follows:


DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🧑‍💻 Where:

  • partition_expression – specifies the columns by which the result set is divided into partitions.
  • sort_expression – specifies the columns by which the rows in each partition are ordered.
  • ASC – optional keyword to sort the rows in ascending order.
  • DESC – optional keyword to sort the rows in descending order.

Example

Suppose we have a table Sales with the following schema and data:

CREATE TABLE Sales (
    SaleID INT,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50),
    TotalSale INT
);

INSERT INTO Sales (SaleID, EmployeeName, Department, TotalSale) VALUES
(1, 'Alice', 'Electronics', 100),
(2, 'Bob', 'Electronics', 300),
(3, 'Charlie', 'Clothing', 150),
(4, 'Daisy', 'Electronics', 180),
(5, 'Eva', 'Clothing', 250);

We want to rank the sales within each department based on the TotalSale amount.

📌 Query:

SELECT
    SaleID,
    EmployeeName,
    Department,
    TotalSale,
    DENSE_RANK() OVER (
        PARTITION BY Department
        ORDER BY TotalSale DESC
    ) AS SaleRank
FROM Sales;

The result set will look like this:

+--------+--------------+-------------+-----------+----------+
| SaleID | EmployeeName | Department  | TotalSale | SaleRank |
+--------+--------------+-------------+-----------+----------+
| 2      | Bob          | Electronics | 300       | 1        |
| 4      | Daisy        | Electronics | 180       | 2        |
| 1      | Alice        | Electronics | 100       | 3        |
| 5      | Eva          | Clothing    | 250       | 1        |
| 3      | Charlie      | Clothing    | 150       | 2        |
+--------+--------------+-------------+-----------+----------+

📌 Explanation:

  • Sales in the Electronics department are ranked as 1 (Bob with 300), 2 (Daisy with 180), and 3 (Alice with 100).
  • Sales in the Clothing department are ranked as 1 (Eva with 250) and 2 (Charlie with 150).

As seen from the results, the ranks within each department are dense, meaning there are no gaps in the ranking sequence. This makes DENSE_RANK a valuable function when continuous ranking without gaps is required.