DB2 SQL — RANK Vs DENSE_RANK: Top Differences

DB2 SQL provides powerful window functions that perform ranking operations within a result set. The functions RANK and DENSE_RANK assign ranks to rows based on specified criteria. However, they differ in how they handle ties within the data.

Table of contents

1. RANK Function
2. DENSE_RANK Function
3. Table Data to Practice
4. Conclusion

RANK Function

The RANK function’s purpose is to assign a unique rank to each distinct row from the result set. If there are ties (i.e., multiple rows with the same value for the ranking criteria), the ranks will have gaps equal to the number of tied rows. For example, if two rows tie for the second rank, the next rank assigned will be 4, not 3.

Here is an example of using the RANK function in DB2 SQL:

SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM 
    employees;

In this example, the RANK function assigns a rank to each employee within their respective departments based on their salaries. The PARTITION BY clause resets the ranking for each department.

DENSE_RANK Function

The DENSE_RANK function is similar to RANK, but it does not leave gaps in the ranking when there are ties. It assigns consecutive ranks to tied rows, ensuring that there are no gaps in the ranking sequence. This can be particularly useful when a continuous sequence of ranks is required.

Here is an example of using the DENSE_RANK function in DB2 SQL:

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

In this example, the DENSE_RANK function’s purpose is to assign a rank to each employee based on their overall salary across all departments. Unlike the RANK function, the DENSE_RANK function does not leave gaps in the ranking sequence, ensuring a continuous and dense ranking for the data.

Table Data to Practice

-- Create a sample 'employees' table for practice
CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert sample data into the 'employees' table
INSERT INTO employees (employee_id, department, salary) VALUES
(1, 'HR', 50000.00),
(2, 'HR', 55000.00),
(3, 'HR', 60000.00),
(4, 'IT', 65000.00),
(5, 'IT', 63000.00),
(6, 'IT', 68000.00);

Conclusion

The RANK and DENSE_RANK are valuable window functions for data analysis, allowing users to identify top performers, outliers, and rankings within a dataset based on specified criteria.