RANK

Purpose

This function computes the rank for each row by adding 1 (one) to the number of rows that precede the current row and are not peers of the current row. It implies that the rows with the same values in the ordering columns have the same rank, and there are gaps in the rank values produced by this function.

Also see DENSE_RANK function which works in a similar fashion but avoids gaps.

Syntax

Usage Notes

• RANK can only be used as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
• The over_clause has to contain an order_clause and must not contain a window_frame_clause.
• This function returns the same value for rows with equal ranking. Therefore, the computed results may contain gaps (as opposed to DENSE_RANK).

Example

Analytic Function

SELECT 
    id, department, current_salary, 
    RANK() OVER (PARTITION BY department ORDER BY current_salary) RANK
FROM employee_table ORDER BY department, current_salary;

Result