Django ORM query GROUP BY multiple columns combined by MAX

Question

I am using Django with MySQL. I have a model similar to the following:

class MM(models.Model):
    a = models.IntegerField()
    b = models.IntegerField()
    c = models.DateTimeField(auto_now_add=True)

I have multiple rows that a is equal to b, and I want to perform the following SQL query:

SELECT a, b, MAX(c) AS max FROM MM GROUP BY b, a;

How can this be done with Django ORM? I have tried different approaches using annotations, but now luck so far.

Thanks a lot!

Answer 1

I think you can do something like:

MM.objects.all().values('b', 'a').annotate(max=Max('c'))

Note that you need to import something to use Max: from django.db.models import Max

values('b', 'a') will give GROUP BY b, a and annotate(...) will compute the MAX in your query.

Answer 2

You can try this also

from django.db.models import Max

mm_list=MM.objects.all().values('b','a').annotate(max=Max('c'))
for mm in mm_list:
    a=mm['a']
    b=mm['b']
    max=mm['max']