Impara Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK | Ranking and Analytic Window Functions

When analyzing data, you often need to assign ranks to rows within specific groups, such as identifying the highest earners in each department or ranking employees by performance. Ranking functions in SQL—specifically ROW_NUMBER, RANK, and DENSE_RANK—are essential tools for these tasks. These functions allow you to generate ordered sequences for rows, either uniquely or with consideration for ties, making them invaluable for analytics, reporting, and business intelligence. You can use them to find top performers, assign awards, or simply organize results for easier interpretation.


              123456
            
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

In this query, you assign a unique rank to each employee within their department, ordering by salary from highest to lowest. The ROW_NUMBER() function generates sequential numbers for each row within the group defined by PARTITION BY department. No two employees in the same department will have the same rank, even if their salaries are identical.

However, not all ranking scenarios are the same. ROW_NUMBER, RANK, and DENSE_RANK each handle ties differently. ROW_NUMBER always assigns a unique, consecutive number to each row, regardless of ties. RANK assigns the same rank to tied values but skips the next rank(s) accordingly, causing gaps. DENSE_RANK also assigns the same rank to tied values but does not leave gaps in the ranking sequence. This distinction becomes crucial when you have multiple employees with the same salary.

For instance, consider employees in the Marketing department with identical salaries. Using RANK() and DENSE_RANK(), you can see how ties are handled.


              12345678
            
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_in_dept
FROM employees
WHERE department = 'Marketing';

Suppose two employees in Marketing have the same salary. Both will receive the same RANK, and the next rank will be skipped. With DENSE_RANK, both still share the same rank, but the next rank is only incremented by one, ensuring no gaps. This difference is critical for reporting and analytics where you want either unique positions, recognition of ties, or a compact ranking list.

1. Which ranking function assigns the same rank to tied values but skips subsequent ranks?

2. What is the main difference between ROW_NUMBER and DENSE_RANK?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 1

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Scorri per mostrare il menu