row_number()

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Returns the current row's index in a serialized row set.

The row index starts by default at 1 for the first row, and is incremented by 1 for each additional row. Optionally, the row index can start at a different value than 1. Additionally, the row index may be reset according to some provided predicate.

row_number( [StartingIndex [, Restart]] )

Learn more about syntax conventions.

The function returns the row index of the current row as a value of type long.

The following example returns a table with two columns, the first column (a) with numbers from 10 down to 1, and the second column (rn) with numbers from 1 up to 10:

range a from 1 to 10 step 1
| sort by a desc
| extend rn=row_number()

The following example is similar to the above, only the second column (rn) starts at 7:

range a from 1 to 10 step 1
| sort by a desc
| extend rn=row_number(7)

The last example shows how one can partition the data and number the rows per each partition. Here, we partition the data by Airport:

datatable (Airport:string, Airline:string, Departures:long)
[
  "TLV", "LH", 1,
  "TLV", "LY", 100,
  "SEA", "LH", 1,
  "SEA", "BA", 2,
  "SEA", "LY", 0
]
| sort by Airport asc, Departures desc
| extend Rank=row_number(1, prev(Airport) != Airport)

Running this query produces the following result: