How to make Django Queryset that selects records with max value within a group

Question

Here is my Django class:

class MyClass(models.Model):
    my_integer = models.IntegerField()
    created_ts = models.DateTimeField(default=datetime.utcnow, editable=False)

I would like to retrieve the instances of MyClass that have the latest created_ts for each unique value of my_integer. I cannot figure out how to do it.

Can someone show my how to do it?

Answer 1

This Will help you

from django.db.models import Count, Max
MyClass.objects.values('my_integer').annotate(count=Count("my_integer"),latest_date=Max('created_ts'))

Data in table

  my_integer      created_ts
    -             -----------
    1 2015-09-08 20:05:51.144321+00:00
    1 2015-09-08 20:08:40.687936+00:00
    3 2015-09-08 20:08:58.472077+00:00
    2 2015-09-08 20:09:08.493748+00:00
    2 2015-09-08 20:10:20.906069+00:00

Output

[
    {'count': 2, 'latest_date': datetime.datetime(2015, 9, 8, 20, 8, 40, 687936, tzinfo=<UTC>), 'my_integer': 1},
    {'count': 2, 'latest_date': datetime.datetime(2015, 9, 8, 20, 10, 20, 906069, tzinfo=<UTC>), 'my_integer': 2},
    {'count': 1, 'latest_date': datetime.datetime(2015, 9, 8, 20, 8, 58, 472077, tzinfo=<UTC>), 'my_integer': 3}
]