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?

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?

• python
• sql
• django
• postgresql

Share Improve this question Follow asked Mar 4 at 10:56 whitebearwhitebear 12.5k2929 gold badges149149 silver badges299299 bronze badges 1

• It might be because you count (see SELECT COUNT(*)). Can you share the ful traceback? – willeM_ Van Onsem Commented Mar 4 at 11:17

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

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.

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')