Forum Discussion

Copper Contributor

Jul 16, 2019

how to select multiple rows the equal max value using summarize

hi, I have a table which contains id, date and value, i.e.: id date value 1 1.1 100 1 1.1 100 1 2.1 300 1 2.1 200 2 1.1 100 2 2.2 200 2 2.2 300...

Yoni

Microsoft

Jul 16, 2019

let T = 
    datatable(id:long, dt:datetime, value:long)
    [
    	1,    datetime(2019-01-01), 100,
    	1,    datetime(2019-01-01), 100,
    	1,    datetime(2019-02-01), 300,
    	1,    datetime(2019-02-01), 200,
    	2,    datetime(2019-01-01), 100,
    	2,    datetime(2019-02-02), 200,
    	2,    datetime(2019-02-02), 300,
    ]
;
T
| summarize dt = max(dt) by id
| join kind=inner T on id, dt
| project id, dt, value