python - django annotate a function that returns the maximum of a field among all objects that has a feature -

suppose have model:

class student(models.model):     class_name = models.charfield()     mark = models.integerfield() 

and want students have highest mark in class. can student has highest mark in classes mentioned in this post. want students have highest mark in class, this:

student.objects.annotate(     highest_mark_in_class=max(         students.objects.filter(class_name=f('class_name'))         .filter(mark=highest_mark_in_class)     ) ) 

i can for loop, large database for loops rather slow. don't know if it's possible write such query in 1 line?

you have use 2 queries that:

import operator functools import reduce django.db.models import max, q  best_marks = student.objects.values('class_name').annotate(mark=max('mark')) q_object = reduce(operator.or_, (q(**x) x in best_marks)) queryset = student.objects.filter(q_object) 

first query gets list of best mark each class.

second query gets students mark , class matches 1 item of list.

note if call .annotate(best_mark=max('mark')) instead of .annotate(mark=max('mark')), have work rename best_mark mark prior passing dictionnary q object. while q(**x) quite convenient.