Skip to main content

RANK

RANK() assigns an integer rank to rows within a group, based on the specified ordering.

Syntax and Parameters​

RANK() OVER (PARTITION BY partition_column ORDER BY order_column [ASC | DESC])

  • PARTITION BY - (optional): Defines the group of rows to rank independently.
  • ORDER BY - (required): Defines the sort order used to assign rank values.
  • Result - The rank is an integer. Tied values share the same rank, and subsequent ranks are skipped.
    • RANK() always returns an integer (NUMBER).

Basic Usage​

To rank orders for each customer by order date:

RANK() OVER (
  PARTITION BY "ORDERS"."O_CUSTKEY"
  ORDER BY "ORDERS"."O_ORDERDATE" DESC
)

If two orders share the same date, they'll share the same rank, and the next rank is skipped.

O_CUSTKEY  O_ORDERDATE   RANK
---------  ------------  ----
123        2025-05-06    1
123        2025-05-06    1
123        2025-05-01    3

Using RANK in Coalesce​

You can use Helper Tokens to dynamically reference columns:

// Template
RANK() OVER (
  PARTITION BY {{SRC_PARTITION}}
  ORDER BY {{SRC_ORDER}} DESC
)

//Example using SRC_NODE
RANK() OVER (
  PARTITION BY "{{SRC_NODE}}"."O_CUSTKEY"
  ORDER BY "{{SRC_NODE}}"."O_ORDERDATE" DESC
)

Dense Rank​

When you use the RANK() function in SQL, it assigns the same rank to rows that are tied in sort order. After a tie, it skips the next rank to maintain position count.

How Rank Works​

You're ranking four values:

Values:  100   100   90   80

Using:

RANK() OVER (ORDER BY value DESC)

You get:

Ranks:     1     1     3     4
  • The two 100s are tied, so both get rank 1.
  • Because rank 2 is “used up” by the tie, the next rank jumps to 3.
  • Then continues normally.

This is standard RANK() behavior and is called a non-dense ranking.

How Dense Rank Works​

If you don’t want to skip ranks when there are ties, use DENSE_RANK() instead:

DENSE_RANK() OVER (ORDER BY value DESC)
Ranks:     1     1     2     3
  • Still ranks the tied 100s as 1
  • But the next distinct value (90) gets 2, not 3