SQL Function: DENSE_RANK()

About SQL Function: DENSE_RANK()

When working with SQL, you might need to rank rows based on specific criteria. The DENSE_RANK() function is a useful tool for this purpose. Unlike the RANK() function, DENSE_RANK() does not leave gaps in the ranking sequence when there are ties.

What is the DENSE_RANK() Function?

The DENSE_RANK() function is a window function that assigns a rank to each row within a partition of a result set. The ranks are assigned based on the order specified in the ORDER BY clause. Unlike the RANK() function, DENSE_RANK() continues the sequence of ranks without gaps.

Syntax of DENSE_RANK()

The basic syntax for DENSE_RANK() is as follows:

DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name)

• PARTITION BY column_name: Divides the result set into partitions to which the DENSE_RANK() function is applied. This clause is optional.

• ORDER BY column_name: Specifies the order in which the DENSE_RANK() function assigns ranks to the rows.

Examples of DENSE_RANK() in Action

Practice Table Creation and Data Insertion

To practice using the DENSE_RANK() function, let's create a table and insert some sample data.

-- Create the Employees table
CREATE OR REPLACE TABLE employees (
    employee_id INT,
    department VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert sample data into the Employees table
INSERT INTO Employees (employee_id, department, first_name, last_name, salary)
VALUES 
(1, 'Sales', 'John', 'Doe', 50000.00),
(2, 'Sales', 'Jane', 'Smith', 60000.00),
(3, 'HR', 'Mike', 'Johnson', 45000.00),
(4, 'HR', 'Emily', 'Davis', 55000.00),
(5, 'IT', 'Chris', 'Brown', 70000.00),
(6, 'Sales', 'Alice', 'White', 60000.00),
(7, 'HR', 'Bob', 'Green', 55000.00);

Example 1: Ranking Employees by Salary

To rank employees within each department based on their salaries without gaps, use the following query:

SELECT 
    employee_id,
    first_name,
    last_name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

Example 2: Ranking Employees Without Partitioning

If you want to rank employees across all departments without partitioning, use the following query:

SELECT 
    employee_id,
    first_name,
    last_name,
    department,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

Benefits of Using DENSE_RANK()

• No Gaps in Ranking: Unlike the RANK() function, DENSE_RANK() does not leave gaps in the ranking sequence, making it ideal for situations where continuous ranking is required.

• Simplifies Ranking Operations: Easily assign ranks to rows based on specific criteria, facilitating tasks like leaderboard creation and performance evaluation.

• Increases Query Flexibility: Apply the function dynamically across partitions and orderings.

Github:

https://github.com/vipinputhanveetil/sql-concepts/blob/main/sql_dense_rank.sql

Conclusion

The DENSE_RANK() function is a valuable tool for SQL developers, allowing you to rank rows based on specific criteria without leaving gaps in the ranking sequence. By mastering its usage, you can enhance your queries and efficiently handle ranking operations in your datasets.

Stay tuned for more articles in this SQL Concepts series as we continue to explore essential SQL functions and techniques!