Forum Discussion
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
i want for each id to get all the lines for the latest date.
result of the table above:
id date value
1 2.1 300
1 2.1 200
2 2.2 200
2 2.2 300
how should I do this query?
Thanks
1 Reply
- Yoni
Microsoft
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
