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',

score 12 · Accepted Answer · answered Oct 16 '18 at 22:49

12

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

answered Oct 16 '18 at 22:49

Deepak Agrawal

136
1
3

2

Note that, if you want to ensure both values are chosen from the same record (e.g. `'col1_1', 'col2_1'` instead of `'col1_1', 'col2_11'`), you can use `any(col1, col2)`. – Chris Long Jun 25 '20 at 06:37

score 0 · Answer 2 · answered Dec 13 '19 at 19:28

Would this work for your needs?

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 col1 = make_set( col1 ), col2 = make_set( col2 ) by id

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

2 Answers2