MODIFIED_RANK

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the rank of the current row within its partition, with gaps, taking the highest row number among tied peers rather than the lowest.

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

None.

• PARTITION BY <expression>: Divides the rows into partitions. modified_rank is computed independently within each partition. Without PARTITION BY, all rows are treated as a single partition.
• ORDER BY <expression>: Determines the order used to compute the rank within each partition. modified_rank requires ORDER BY to produce a meaningful result.

Returns a bigint.

• modified_rank differs from RANK in how it handles ties: for a group of tied peer rows, modified_rank assigns the rank equal to the position of the tied group’s last (highest-numbered) peer, while rank assigns the position of the group’s first (lowest-numbered) peer.
• Like rank, modified_rank leaves gaps in the sequence after a tie. This differs from DENSE_RANK, which never has gaps.
• For a row with no ties, rank and modified_rank return the same value.
• For general OVER clause syntax, including PARTITION BY, ORDER BY, and frame clauses, see Window Functions.
• To reuse a window definition across multiple function calls, define it once in a WINDOW clause. See SELECT.

Contrast how rank and modified_rank handle tied salaries within each department.

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

Result:

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

In the develop department, empno 10 and 11 are tied at salary 5200. rank assigns them both 2 (the position of the first peer), while modified_rank assigns them both 3 (the position of the last peer).

• RANK
• DENSE_RANK
• ROW_NUMBER
• Aggregate Functions
• Window Functions