RANK

Syntax

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

What it does

RANK() gives the same rank to tied rows and then skips the next numbers.

Example: values 10, 10, 20 become ranks 1, 1, 3.

Example

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

Difference from ROW_NUMBER()

• ROW_NUMBER() gives every row a unique number
• RANK() gives ties the same rank and leaves gaps
• DENSE_RANK() gives ties the same rank without gaps

Safety questions

• do ties need to be grouped or differentiated?
• is the ordering stable enough to support repeatable ranking?
• do you need dense ranking rather than standard ranking?