Get rows from the intermediate table by max value of a column

[harrychen]

I have an intermediate table from some query:

... | table Stock_price_difference, start_time, end_time, company

Stock_price_difference	start_time	end_time	company
1.3	10:01	10:20	Apple
0.3	11:00	11:05	Apple
1.1	11:30	11:35	Apple
0.4	09:10	09:18	MS
0.8	09:50	10:00	MS
0.1	10:00	10:10	MS
1	10:15	10:20	Amazon
1.1	11:10	11:15	Amazon

I would like to get the rows by max stock price difference for each company:

Stock_price_difference	start_time	end_time	company
1.3	10:01	10:20	Apple
0.8	09:50	10:00	MS
1.1	11:10	11:15	Amazon

How can I do that?

Thanks.

[yannK]

They are many approaches, here is the quick and hack :
- using a sort and a dedup on the company, to keep the event highest value in the Stock-price-difference per company.

...| sort company -Stock_price_difference | dedup company | table Stock_price_difference, start_time, end_time, company

result is :
Stock_price_difference start_time end_time company
1.1 11:10 11:15 Amazon
1.3 10:01 10:20 Apple
0.8 09:50 10:00 MS


[harrychen]

That's even sleeker, thanks!

[somesoni2 · Accepted solution]

This should solve the problem:

..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF<2 | fields - CountF

This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)

[harrychen]

Awesome! That works.