Find X largest values of field by another field

[bowesmana]

I have a set of events that are racing results. One result/month for 10 months of the year. Points are awarded to runners for each race during the year. In order to find the annual winner, the monthly points are added and the winner is the person with the most points. Easy...

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015 
| stats sum(Points) as Total count as Runs count(eval(Place==1)) as Wins by Name, Gender 
| sort - Total

However, the catch is that only the top 8 races for a runner count to the total, so if the runner runs 9 or 10 runs, then the lowest two points are not counted, so the sum(Points) above can't be used.

I tried to do a sort by points for all runners and the idea was to then take only the first 8 records of each Name field, but I can't make this work.

Any idea how to do this?

[javiergn · Accepted solution]

You could try something like this by using streamstats:

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015
| sort limit=0 Name, Gender, - Points
| streamstats count as id by Name, Gender, Points
| where id <= 8
| stats sum(Points) as Total count as Runs count(eval(Place==1)) as Wins by Name, Gender 
| sort - Total

[bowesmana]

Thanks javiergn, that almost worked, the key change needed was to the initial streamstats as it didn't need to group by Gender or Points as id would count the number of different points values, hence never get to 8.

In order to keep the total Runs and Wins correct in case they had 9 or 10, I put an eventstats in before the where clause, so it made the final search

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015
| eventstats count as Runs count(eval(Place==1)) as Wins by Name
| sort limit=0 Name, Gender, - Points
| streamstats count as id by Name
| where id <= 8
| stats sum(Points) as Total values(Runs) as Runs values(Wins) as Wins by Name, Gender 
| sort - Total 

Cheers