Oracle DBA

Ranking functions are part of SQL analytic functions and are widely used in reporting, analytics, and business intelligence. The most common ones are RANK, DENSE_RANK, and ROW_NUMBER. While they seem similar, their behavior differs when handling duplicate values.

Let’s explore them through practical examples.

Example 1: Ranking Employees by Salary within Each Department

Here we rank employees by salary within their department.

SELECT department_id, employee_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_number
FROM employees;

Explanation:

• RANK assigns the same rank to ties, leaving gaps after duplicates.
• DENSE_RANK assigns the same rank to ties but keeps the sequence continuous.
• ROW_NUMBER always gives a unique sequential number regardless of duplicates.

This is useful when preparing compensation reports within each department.

Example 2: Ranking Departments by Average Salary

We compute the average salary per department and rank departments by their averages.

SELECT department_id, AVG(salary) AS avg_salary,
       RANK() OVER (ORDER BY AVG(salary) DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY AVG(salary) DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY AVG(salary) DESC) AS row_number
FROM employees
GROUP BY department_id;

This ranking highlights which departments pay the most on average, allowing HR and management to compare compensation levels across departments.

Example 3: Identifying the Top 3 Highest Salaries in Each Department

Using ROW_NUMBER, we can rank employees in each department and filter to the top 3.

SELECT department_id, employee_id, salary
FROM (
    SELECT department_id, employee_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_number
    FROM employees
) t
WHERE row_number <= 3;

This query isolates the top earners per department, useful for recognition programs or compensation benchmarking.

Example 4: Ranking Employees by Hire Date within Each Job

Here we rank employees by hire date to understand seniority within job roles.

SELECT job_id, employee_id, hire_date,
       RANK() OVER (PARTITION BY job_id ORDER BY hire_date) AS rank,
       DENSE_RANK() OVER (PARTITION BY job_id ORDER BY hire_date) AS dense_rank,
       ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY hire_date) AS row_number
FROM employees;

Explanation:

• RANK and DENSE_RANK show how many employees share the same hire date.
• ROW_NUMBER enforces a strict order, so every employee has a unique position.

This is practical for succession planning or determining the most senior staff in each role.

Example 5: Ranking Sales Performance (Assume Sales Data)

Suppose we have a sales table with employee_id, department_id, and sales_amount. We can rank employees by their sales performance within departments.

SELECT department_id, employee_id, sales_amount,
       RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dense_rank,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS row_number
FROM sales;

This type of ranking is commonly used in performance dashboards to highlight top salespeople in each department.

Example 6: Determining Salary Position within the Entire Organization

Finally, we can rank employees across the whole organization by salary.

SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

Explanation:
This provides a single ordered ranking of all employees, independent of department or job. It is useful for identifying the highest and lowest earners across the company.

Key Takeaways

• RANK, DENSE_RANK, and ROW_NUMBER are all window functions used for ranking.
• RANK leaves gaps in ranking after ties.
• DENSE_RANK avoids gaps, producing a continuous ranking sequence.
• ROW_NUMBER always assigns unique sequential numbers.
• These functions are extremely useful for reporting top-N queries, identifying leaders, seniority analysis, and compensation benchmarking.