how to select multiple rows the equal max value using summarize

Copper Contributor

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