How can I get all but the last row in a KQL query?

Question

I've got a KQL query, and I want to strip out the last row of data. However, the number of rows returned is variable, so I can't use take (aka limit) because I don't know what number to pass it.

Is there any way to get the number of rows returned and pass that to limit?

Answer 1

I couldn't find any way to do this in one query, but How to convert row count result to another column in Kusto query inspired me to do it in two:

let result_table = my_table
| query here ...
| order by x // you have to sort to get the same row removed every time!
;
// Now get all but the final row
let num_rows = toscalar(result_table | summarize count());
result_table | take num_rows - 1

Answer 2

Firstly my table looks like :

How can I get all but the last row in a KQL query?I couldn't find any way to do this in one query

You could do this in one query using below KQL query:

RithTest
| extend new_column = 0
| sort by new_column asc
| extend rn=row_number()
| project-away new_column
| order by rn desc
| extend new_column = 0
| sort by new_column asc
| extend rn1=row_number()
| where rn1>1
| order by rn asc
| project-away rn,new_column,rn1

Output:

Fiddle.

Answer 3

Just to add in my answer here, you can do probably what you want with Windows functions and specifically next().

let T = range Timestamp from ago(3h) to ago(1h) step 1m
| extend Value = rand(999);
T
| order by Timestamp asc
| where isnotnull(next(Value)) //If the next value exists it isn't our last record

Timestamp	Value
2024-03-26T04:29:59.9749979Z	203
2024-03-26T04:30:59.9749979Z	866
2024-03-26T04:31:59.9749979Z	219
2024-03-26T04:32:59.9749979Z	144
2024-03-26T04:33:59.9749979Z	350
2024-03-26T04:34:59.9749979Z	651
2024-03-26T04:35:59.9749979Z	216
2024-03-26T04:36:59.9749979Z	390
2024-03-26T04:37:59.9749979Z	554
2024-03-26T04:38:59.9749979Z	735
2024-03-26T04:39:59.9749979Z	799
2024-03-26T04:40:59.9749979Z	940
etc	etc