row_rank_dense()

Learn how to use the row_rank_dense() function to return the current row’s dense rank in a serialized row set.

Returns the current row’s dense rank in a serialized row set.

The row rank starts by default at 1 for the first row, and is incremented by 1 whenever the provided Term is different than the previous row’s Term.

Syntax

row_rank_dense ( Term )

Parameters

NameTypeRequiredDescription
Termstring✔️An expression indicating the value to consider for the rank. The rank is increased whenever the Term changes.
restartboolIndicates when the numbering is to be restarted to the StartingIndex value. The default is false.

Returns

Returns the row rank of the current row as a value of type long.

Example

The following query shows how to rank the Airline by the number of departures from the SEA Airport using dense rank.

datatable (Airport:string, Airline:string, Departures:long)
[
  "SEA", "LH", 3,
  "SEA", "LY", 100,
  "SEA", "UA", 3,
  "SEA", "BA", 2,
  "SEA", "EL", 3
]
| sort by Departures asc
| extend Rank=row_rank_dense(Departures)

Output

AirportAirlineDeparturesRank
SEABA21
SEALH32
SEAUA32
SEAEL32
SEALY1003

The following example shows how to rank the Airline by the number of departures per each partition. Here, we partition the data by Airport:

datatable (Airport:string, Airline:string, Departures:long)
[
  "SEA", "LH", 3,
  "SEA", "LY", 100,
  "SEA", "UA", 3,
  "SEA", "BA", 2,
  "SEA", "EL", 3,
  "AMS", "EL", 1,
  "AMS", "BA", 1
]
| sort by Airport desc, Departures asc
| extend Rank=row_rank_dense(Departures, prev(Airport) != Airport)

Output

AirportAirlineDeparturesRank
SEABA21
SEALH32
SEAUA32
SEAEL32
SEALY1003
AMSEL11
AMSBA11