RANK

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the rank of the current row with gaps; same as row_number of its first peer.

1rank() OVER ( [ PARTITION BY <expression> [, ...] ] ORDER BY <expression> [, ...] )

rank takes no arguments.

Returns a bigint.

• rank depends on the ORDER BY clause of the window definition to determine the rank of each row. Without ORDER BY, all rows are peers and every row gets a rank of 1.
• Rows that aren’t unique in the ORDER BY columns are peers and receive the same rank.
• After a group of tied rows, the next rank skips ahead by the number of tied rows and leaves a gap. For example, two rows tied at rank 2 are followed by rank 4, not rank 3.
• rank contrasts with dense_rank, which ranks without gaps, and with modified_rank, which also produces gaps but assigns the tied group the rank of its last peer instead of its first. For more information, see DENSE_RANK and MODIFIED_RANK.
• For more information about the OVER clause and window frames, see Window Function Call Syntax. For more information about naming a window definition with a WINDOW clause, see SELECT.

Rank employees by salary within each department.

1SELECT
2    depname,
3    rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank,
4    salary,
5    empno
6FROM empsalary
7ORDER BY depname, rank;

Result:

1depname   | rank | salary | empno
2----------+------+--------+------
3develop   |    1 |   6000 |     8
4develop   |    2 |   5200 |    10
5develop   |    2 |   5200 |    11
6develop   |    4 |   4500 |     9
7develop   |    5 |   4200 |     7
8personnel |    1 |   3900 |     2
9personnel |    2 |   3500 |     5
10sales     |    1 |   5000 |     1
11sales     |    2 |   4800 |     4
12sales     |    2 |   4800 |     3
13(10 rows)

The rank function produces a numerical rank for each unique ORDER BY value in the current row’s partition and uses the order defined by the ORDER BY clause. In the develop department, empno 10 and 11 tie for rank 2, so the next row, empno 9, receives rank 4 rather than rank 3.

Rank all rows in a table by salary without dividing them into partitions.

1SELECT empno, salary, rank() OVER (ORDER BY salary DESC)
2FROM empsalary;

Omitting PARTITION BY treats all rows as a single partition, so the rank reflects each employee’s salary standing across the entire table.

• ROW_NUMBER
• DENSE_RANK
• MODIFIED_RANK
• Aggregate Functions
• Window Functions