Combine Values into one event then search if one of the values are contained

[geraldcontreras]

Hi,
Thanks in advance

This is hard one to put well in the title

Basically i have sets of data which contain Students Scores for tests. Students can take these tests multiple times.
I need a search that will show only events where the student has never scroed greater than 80

Sample Data (fields "Display_Name" and "result":

Display_Name result
John_Doe 20
John_Doe 60
John_Doe 80
Jane_Doe 95
Jack_Doe 20

Results i need (as he is the only person not to have scored 80 or higher:
Jack_Doe

i cant just simply use
"where result < 80"
because then John_doe will be included.

I need something that will exclude someone who has scored 80 or higher.

I have tried all matter of combinations, which i wont list as i find sometimes its best for people to approach without prior conception.

Thanks you all

[geraldcontreras · Accepted solution]

I found the answer by using max
it was staring me in the face the whole time

| stats max(Score) as result by Display_Name | where result < 80

must of been a Friday! 😄

[renjith_nair]

@geraldcontreras , converted your comment to answer. You may accept it as answer and close the thread 🙂