How to Do SELECT MAX in Django?

When working with databases in Django, we often need to find the highest value of a specific field in a model. Let's say, find the book with the highest price, the product with the highest discount, etc. This is like doing a SELECT MAX query in SQL. In this article, we'll learn how to find the max value using the `aggregate()` and `order_by()`, methods from the Django Database Function.

Method 1 - Using Django aggregate()

Django provides the aggregate() method, which allows us to perform aggregate queries on a model. To use aggregate(), we need to import the Max aggregation function from django.db.models.

from django.db.models import Max

Code Example:

Let's take an example, we have a Book model with a price field and title field. Now, we want to retrieve the maximum price of all books.

models.py: We have added a min value validator so that the prices of a book can never be less than 0.00.

# models.py
class Book(models.Model):
    title = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=5,
                                decimal_places=2,
                                validators=[MinValueValidator(Decimal('0.01'))])

    def __str__(self) -> str:
        return f'{self.title}_price_{str(self.price)}'
    

Let's create some Book objects using the Django shell and perform aggregation.

python manage.py shell

Django Shell:

>>> from myapp.models import Book

>>> Book.objects.create(title='Book 1', price=10.99)
<Book: Book 1_price_10.99>

>>> Book.objects.create(title='Book 2', price=20.99)
<Book: Book 2_price_20.99>

>>> Book.objects.create(title='Book 3', price=30.99)
<Book: Book 3_price_30.99>

>>> Book.objects.create(title='Book 4', price=40.99)
<Book: Book 4_price_40.99>

>>> Book.objects.create(title='Book 5', price=50.99)
<Book: Book 5_price_50.99>

Get the Maximum Price

To get the maximum price, we can use the aggregate() method as follows:

In this example, Book.objects.aggregate(Max('price')) returns a dictionary with a single key-value pair, where the key is price__max and the value is the maximum price. We then access the maximum price using the key price__max.

>>> from django.db.models import Max

>>> Book.objects.aggregate(Max('price'))
{'price__max': Decimal('50.9900000000000')}

# Extract data
>>> Book.objects.aggregate(Max('price'))['price__max'] 
Decimal('50.9900000000000')

The default key name will always be like <field_name>__<aggreate_function_name>. Also, we can pass the default value in case there are no instances in the database.

Passing the Default value

Let's see how can we change the default name and pass a default value.

>>> Book.objects.aggregate(max_price=Max('price', default=0)) 
{'max_price': Decimal('50.9900000000000')}

>>> data = Book.objects.aggregate(max_price=Max('price', default=0)) 
>>> data['max_price']
Decimal('50.9900000000000')

In the above code snippet, we have changed the default name from price__max to max_price.

Method 2 - Using Django order_by()

We can use the order_by() method to find the maximum value without using the Max function.

The SQL equivalent is:

SELECT price FROM book ORDRE BY price DESC LIMIT 1;

Let's find all books ordered by prices in descending order:

>>> Book.objects.all().order_by('-price')
<QuerySet [<Book: Book 5_price_50.99>, <Book: Book 4_price_40.99>, <Book: Book 3_price_30.99>,
<Book: Book 2_price_20.99>, <Book: Book 1_price_10.99>]>

To find the max price, we only need to retrieve the first instance.

>>> Book.objects.all().order_by('-price').first()
<Book: Book 5_price_50.99>
>>>
>>> Book.objects.all().order_by('-price').first().price
Decimal('50.99')

In this example, we use order_by('-price') to order the books by price in descending order, and then use first() to get the first book, which has the maximum price.

In case there is no instance in the database, the query set would be empty and we would need to apply a check.

Find Instance with Max Price

With the aggregate function, we can only find the max_price. To find the Book with the maximum price, we will need to make a few changes. But with the order_by() method, we can easily find the book with the maximum price. As we already did in the above example.

>>> Book.objects.filter(price=Book.objects.aggregate(
                                                                             max_price=Max('price')
                                                                              )['max_price']) 
<QuerySet [<Book: Book 5_price_50.99>]>

>>> Book.objects.filter(price=Book.objects.aggregate(max_price=Max('price'))['max_price']).first()
<Book: Book 5_price_50.99>

In the above example, we have applied a filter Book having maximum prices and retrieving the first instance.

Conclusion

In this article, we've learned how to get the maximum value of a field in Django using the `aggregate()` and `order_by()` methods. By using the `Max` function, we can easily find the highest value in a model, like the maximum price of a product or the top score in a game.