python - In Django ORM: Select record from each group with maximal value of a given attribute -

say have 3 models follows representing prices of goods sold @ several retail locations of same company:

class store(models.model):     name = models.charfield(max_length=256)     address = models.textfield()  class product(models.model):     name = models.charfield(max_length=256)     description = models.textfield()  class price(models.model):     store = models.foreignkey(store)     product = models.foreignkey(product)     effective_date = models.datefield()     value = models.floatfield() 

when price set, set on store-and-product-specific basis. i.e. same item can have different prices in different stores. , each of these prices has effective date. given store , given product, currently-effective price 1 latest effective_date.

what's best way write query return currently-effective price of items in stores?

if using pandas, myself dataframe columns ['store', 'product', 'effective_date', 'price'] , run

dataframe\     .sort_values(columns=['store', 'product', 'effective_date'], ascending=[true, true, false])\     .groupby('store', 'product')['price'].first() 

but there has way of doing directly on database level. thoughts?

if dbms postgresql can use distinct combined order_by way :

price.objects.order_by('store','product','-effective_date').distinct('store','product') 

it give latest prices product/store combinations.

there tricks distinct, have @ docs here : https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.queryset.distinct