“greatest-n-per-group” query in Django 2.0?

Question

Basically, I want to do this but with django 2.0.

If I try:

Purchases.objects.filter(.....).annotate(my_max=Window( expression=Max('field_of_interest'), partition_by=F('customer') ) )

I get back all the rows but with the my_max property added to each record.

Answer 1

If you are using PostgreSQL:

Purchases.objects.filter(.....).order_by(
    'customer', '-field_of_interest'
).distinct('customer')

UPDATE: Window expressions are not allowed in filter, so following methods does not work. Please refer to this answer for up-to-date solution

or with Window expression

Purchases.objects.filter(.....).annotate(my_max=Window(
    expression=Max('field_of_interest'),
    partition_by=F('customer')
    )
).filter(my_max=F('field_of_interest'))

but latter can yield multiple rows per customer if they have the same field_of_interest

Another Window, with single row per customer

Purchases.objects.filter(.....).annotate(row_number=Window(
        expression=RowNumber(),
        partition_by=F('customer'),
        order_by=F('field_of_interest').desc()
        )
    ).filter(row_number=1)