How to do SELECT MAX in Django?

Question

I have a list of objects how can I run a query to give the max value of a field:

I'm using this code:

def get_best_argument(self):
    try:
        arg = self.argument_set.order_by('-rating')[0].details
    except IndexError:
        return 'no posts'
    return arg

rating is an integer

Answer 1

See this. Your code would be something like the following:

from django.db.models import Max
# Generates a "SELECT MAX..." query
Argument.objects.aggregate(Max('rating')) # {'rating__max': 5}

You can also use this on existing querysets:

from django.db.models import Max
args = Argument.objects.filter(name='foo') # or whatever arbitrary queryset
args.aggregate(Max('rating')) # {'rating__max': 5}

If you need the model instance that contains this max value, then the code you posted is probably the best way to do it:

arg = args.order_by('-rating')[0]

Note that this will error if the queryset is empty, i.e. if no arguments match the query (because the [0] part will raise an IndexError). If you want to avoid that behavior and instead simply return None in that case, use .first():

arg = args.order_by('-rating').first() # may return None

Answer 2

Django also has the 'latest(field_name = None)' function that finds the latest (max. value) entry. It not only works with date fields but also with strings and integers.

You can give the field name when calling that function:

max_rated_entry = YourModel.objects.latest('rating')
return max_rated_entry.details

Or you can already give that field name in your models meta data:

from django.db import models

class YourModel(models.Model):
    #your class definition
    class Meta:
        get_latest_by = 'rating'

Now you can call 'latest()' without any parameters:

max_rated_entry = YourModel.objects.latest()
return max_rated_entry.details

Answer 3

I've tested this for my project, it finds the max/min in O(n) time:

from django.db.models import Max

# Find the maximum value of the rating and then get the record with that rating. 
# Notice the double underscores in rating__max
max_rating = App.objects.aggregate(Max('rating'))['rating__max']
return App.objects.get(rating=max_rating)

This is guaranteed to get you one of the maximum elements efficiently, rather than sorting the whole table and getting the top (around O(n*logn)).

Answer 4

sol 01:

from .models import MyMODEL

max_rating = MyMODEL.objects.order_by('-rating').first()

sol 02:

from django.db.models import Max
from .models import MyMODEL

max_rating = MyMODEL.objects.aggregate(Max('rating'))

Answer 5

If you also want to get a value other than None in case the table is empty (e.g. 0), combine Max with Coalesce:

from django.db.models import Max, Value
from django.db.models.functions import Coalesce

max_rating = SomeModel.objects.aggregate(
    max_rating=Coalesce(Max('rating'), Value(0))
)['max_rating']

Answer 6

To maybe improve on @afahim answer with regards to @Raydel Miranda comment, if you want a random comment. If you want all, then use just the filter

from django.db.models import Max

# Find the maximum value of the rating and then get the record with that rating. 
# Notice the double underscores in rating__max
max_rating = App.objects.aggregate(Max('rating'))['rating__max']
return App.objects.filter(rating=max_rating).first()

Answer 7

maybe it will help someone's trouble, in CBV

def get_queryset(self):
    sorgu = Sunum.objects.values('id', 'firma', 'projeadi', 'sunumdurum__durum', 'sunumdurum__aciklama'
                           ).annotate(max_rank=Max('sunumdurum__kayittarihi'))
    szlk={}
    for sor in sorgu :
        ana = sor['id'], sor['firma'], sor['projeadi']
        dana = sor['sunumdurum__durum'], sor['sunumdurum__aciklama'], sor['max_rank']
        szlk.setdefault(ana, dana)
    return szlk

Answer 8

For future readers you can use annotate instead of aggregate if you need the record / other fields and not just the max value.

i.e equivalent to this sql query

SELECT
  first_name,
  last_name,
  Max(height) max_height
FROM patients;

Now one problem is passing an aggregate function in annotate will trigger a group by

from django.db.models import Max

Patients.objects.values('height').annotate(max_height=Max('height'))

>>>
SELECT "patients"."height",
       MAX("patients"."height") AS "max_height"
FROM "patients"
GROUP BY "patients"."height"

The workaround is to use the Func expression

from django.db.models F, Func

Patients.objects.annotate(max_height=Func(F('height'), function='Max'))\
.values('first_name', 'last_name', 'max_height')

>>>
SELECT "patients"."first_name",
       "patients"."last_name",
       Max("patients"."height") AS "max_height"
FROM "patients"

see this if you need to do this in a subquery