CODEWITHSUNDEEP
azure-data-explorerkql
MaxDev
MaxDev

Reputation: 147

Get first value using condition

Case:

I'm trying to display fist name using a condition in Kusto. For Example:

first_name second_name type score date.
John Adam student 67. 2021-8-4
John Adam student 89. 2021-8-3
John Adam student 75. 2021-8-2
James Smith student 80. 2021-8-2
Sam Miles student 69. 2021-8-3

Query needed:

A query made the following: if first name contains "J" then get first value of score column, if not let the value as is

Expected result:

first_name second_name score
John Adam 67.
James Smith 80.
Sam Miles 69.

Search for such operator like case or iff not success

Upvotes: 0

Views: 1547

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

you could try this:

let T = datatable(first_name:string, second_name:string, type:string, score:int, timestamp:datetime)
[
    'John',  'Adam',  'student', 67, datetime(2021-08-04),
    'John',  'Adam',  'student', 89, datetime(2021-08-03),
    'John',  'Adam',  'student', 75, datetime(2021-08-02),
    'James', 'Smith', 'student', 80, datetime(2021-08-02),
    'Sam',   'Miles', 'student', 69, datetime(2021-08-03),
]
;
T
| where first_name startswith "J"
| summarize arg_max(timestamp, *) by first_name
| union (
    T
    | where first_name !startswith "J"
)
| project first_name, second_name, score
first_name second_name score
Sam Miles 69
John Adam 67
James Smith 80

Upvotes: 1

Related Questions