Ordering data after distinct

I distinct data by sku_id

    queryset = self.filter_queryset(queryset.order_by('sku_id','id').distinct('sku_id'))

However this result is not sorted by id,

then I try to

    queryset = self.filter_queryset(queryset.order_by('sku_id','id').distinct('sku_id').order_by('id'))

However this shows the error

ProgrammingError at /api/myfetch/
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT COUNT(*) FROM (SELECT DISTINCT ON ("myapp_produc...

Is it possible to sort the column after distinct?

When you use distinct('sku_id') in Django it translates to DISTINCT ON (sku_id) in PostgreSQL. It's like a feature in PostgreSQL that requires to point in ORDER BY the same column as in DISTINCT.

In your second query you try to reorder by id, which PostgreSQL doesn't allow.

Solution:

queryset = self.filter_queryset(
    queryset.order_by('sku_id', 'id').distinct('sku_id')
).order_by('id')

Last time I checked, you need to use a Subquery to get the expected behavior with postgres backend:

from django.db.models import Subquery

your_objects = YourObjectClass.objects.filter(sku_id__in=Subquery(YourObjectClass.objects.distinct('sku_id').values('sku_id'))).order_by('id')

This should get you records with distinct sku_ids ordered by ascending id.

Back to Top