row_rank_dense()
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
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 )
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| Term | string |
✔️ | An expression indicating the value to consider for the rank. The rank is increased whenever the Term changes. |
| restart | bool |
Indicates 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
| Airport | Airline | Departures | Rank |
|---|---|---|---|
| SEA | BA | 2 | 1 |
| SEA | LH | 3 | 2 |
| SEA | UA | 3 | 2 |
| SEA | EL | 3 | 2 |
| SEA | LY | 100 | 3 |
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
| Airport | Airline | Departures | Rank |
|---|---|---|---|
| SEA | BA | 2 | 1 |
| SEA | LH | 3 | 2 |
| SEA | UA | 3 | 2 |
| SEA | EL | 3 | 2 |
| SEA | LY | 100 | 3 |
| AMS | EL | 1 | 1 |
| AMS | BA | 1 | 1 |