[Solved]-Filtering Django Query by the Record with the Maximum Column Value

by

5👍

✅

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.created
FROM person_person p1, (
    SELECT name, MAX(created) AS max_created
    FROM person_person
    GROUP BY name
) AS p2
WHERE p1.name = p2.name AND p1.created = p2.max_created
''')
👤Zach Kelling

3👍

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 of
each set of rows where the given expressions evaluate to equal. The
DISTINCT ON expressions are interpreted using the same rules as for
ORDER BY (see above). Note that the "first row" of each set is
unpredictable unless ORDER BY is used to ensure that the desired row
appears first.

Using the Django ORM:


Person.objects.order_by('name', '-created').distinct('name')

Generated SQL:

select distinct on (name)
    ...
from person_person
order by name, created desc
👤trecouvr

1👍

If your backend is PostgreSQL Roman Pekar gave a good answer for this question.

👤Nikolai Golub

Leave a comment