List top values in field b for different values in field

[stephen123]

Hi, given the following columns c1, c2 and time

c1 c2 time

a 1 10.01

a 2 10.02

a 3 10.03

b 4 10.04

b 5 10.05

b 6 10.06

c 9 10.07

c 8 10.08

c 7 10.09

I want to get the top value from each of c1, with the associated time so the out put would be

a 3 10.03

b 6 10.06

c 9 10.07

Thanks

[martin_mueller · Accepted solution]

Usually you'd do this:

... | stats max(c2) by c1

However, that won't let you copy over the relevant timestamp... as a way out, consider something like this:

... | eventstats max(c2) as max_c2 by c1 | where c2==max_2 | dedup c1

First add max(c2) to every event, grouped by c1... then keep only the rows with the maximum... then throw out duplicates in case two events for one c1 value have identical c2 values at the maximum.

[pradeepkumarg]

I have a similar situation where I want top three values. How can I achieve it?

[stephen123]

Both work perfect - thanks

[HattrickNZ]

this does not show me anything in stats or visualisation, am i missing something?