Filtering Django Query by the Record with the Maximum Column Value
You'll probably just want to use raw SQL here, the raw()
manager method facilitates this, allowing you to return model instances from your query. The only trick is that the raw query needs to include the primary key. This should probably work for you (unless you have the primary key set to something other than id
):
latest_phone_numbers = Person.objects.raw('''SELECT p1.id, p1.name, p1.phone, p1.createdFROM person_person p1, ( SELECT name, MAX(created) AS max_created FROM person_person GROUP BY name) AS p2WHERE p1.name = p2.name AND p1.created = p2.max_created''')
Update : if you are using PostgreSQL, you can use the ORM with .distinct()
From PostgreSQL documentation:
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row ofeach set of rows where the given expressions evaluate to equal. TheDISTINCT ON expressions are interpreted using the same rules as forORDER BY (see above). Note that the "first row" of each set isunpredictable unless ORDER BY is used to ensure that the desired rowappears first.
Using the Django ORM:
Person.objects.order_by('name', '-created').distinct('name')
Generated SQL:
select distinct on (name) ...from person_personorder by name, created desc