[Django]-Django: Record with max element

by

30👍

✅

The SQL required would be something like this:

SELECT *
FROM STUDENT
WHERE marks = (SELECT MAX(marks) FROM STUDENT)

To do this via Django, you can use the aggregation API.

max_marks = Student.objects.filter(
    subject='Maths'
).aggregate(maxmarks=Max('marks'))['maxmarks']
Student.objects.filter(subject='Maths', marks=max_marks)

Unfortunately, this query is actually two queries. The max mark aggregation is executed, the result pulled into python, then passed to the second query. There’s (surprisingly) no way to pass a queryset that’s just an aggregation without a grouping, even though it should be possible to do. I’m going to open a ticket to see how that might be fixed.

Edit:

It is possible to do this with a single query, but it’s not very obvious. I haven’t seen this method elsewhere.

from django.db.models import Value

max_marks = (
    Student.objects
           .filter(subject='Maths')
           .annotate(common=Value(1))
           .values('common')
           .annotate(max_marks=Max('marks'))
           .values('max_marks')
)

Student.objects.filter(subject='Maths', marks=max_marks)

If you print this query in the shell you get:

SELECT 
       "scratch_student"."id", 
       "scratch_student"."name", 
       "scratch_student"."subject", 
       "scratch_student"."marks" 
  FROM "scratch_student" 
 WHERE ( 
       "scratch_student"."subject" = Maths 
   AND "scratch_student"."marks" = (
       SELECT 
              MAX(U0."marks") AS "max_marks" 
         FROM "scratch_student" U0 
        WHERE U0."subject" = Maths))

Tested on Django 1.11 (currently in alpha). This works by grouping the annotation by the constant 1, which every row will group into. We then strip this grouping column from the select list (the second values(). Django (now) knows enough to determine that the grouping is redundant, and eliminates it. Leaving a single query with the exact SQL we needed.

👤Josh Smeaton

2👍

If you meant that you need one record with the highest mark in Maths, I think the use of SQL LIMIT is more obvious and preferable:

Student.objects.filter(subject='Maths').order_by('-marks')[:1].get()

The SQL will be like this:

SELECT * FROM student WHERE subject = 'Maths' ORDER BY marks DESC LIMIT 1

But keep in mind that there may be several students with the highest mark and you will get a random one.

👤dtatarkin

0👍

With a naive database table, there is theoretically no possible way the database can retrieve the max value for you without first sorting. Just think about it, how can the database know which is the max value unless it looked at every single row?

Of course, that’s with a very naive setup. Luckily you have two options available:

  1. use an index. If you create an index on that column, sorting can usually take advantage of the index – saving you a full table scan.

  2. normalize (aka precompute). Create another table somewhere that stores the max value, and make sure you check/update it every time a Student object is added/modified/deleted.

Without knowing more of the requirements, I strongly suggest using the index.

Check out: https://docs.djangoproject.com/en/dev/ref/models/fields/#db-index

👤shu zOMG chen

0👍

from django.db.models import Max

temp = Student.objects.filter(subject='Math').aggregate(Max('marks'))
record = Student.objects.filter(Q(subject='Math') & Q(subject=temp['marks__max']))
👤naveen kumar

-1👍

This question can be helpfull to you:
How to do SELECT MAX in Django?

Just use aggregation.

from django.db.models import Max
Student.objects.filter(subject='Math').aggregate(Max('marks'))

Not tested, but should work. 🙂

👤zeroos

Leave a comment