How to write a Kusto query to select only the rows that have unique values in one field

Question

Having this input:

let t1 = datatable(id:string, col1:string, col2:string)
[
    '1', 'col1_1', 'col2_1',
    '2', 'col1_2', 'col2_2',
    '3', 'col1_3', 'col2_3',
    '4', 'col1_4', 'col2_4',
    '1', 'col1_1', 'col2_11',
];
t1 
| distinct id, col1

I need a query that will select only rows with unique values in "id" field. I understand that there are two possible outputs:

Output 1:

'1', 'col1_1', 'col2_1',
'2', 'col1_2', 'col2_2',
'3', 'col1_3', 'col2_3',
'4', 'col1_4', 'col2_4',

Output 2:

'2', 'col1_2', 'col2_2',
'3', 'col1_3', 'col2_3',
'4', 'col1_4', 'col2_4',
'1', 'col1_11', 'col2_11',

Answer 1

You can make use of any() aggregate function to pick up the col1 and col2 values based on unique values in 'id' column.

let t1 = datatable(id:string, col1:string, col2:string)
[
    '1', 'col1_1', 'col2_1',
    '2', 'col1_2', 'col2_2',
    '3', 'col1_3', 'col2_3',
    '4', 'col1_4', 'col2_4',
    '1', 'col1_1', 'col2_11',
];
t1 
| summarize any(col1), any(col2) by id