Orderby and distinct at the same time

I have tabels like this

id sku
1  C 
2  B
3  C
4  A

Finally I want to get the raws like this.

1 C
2 B
4 A

At first I use distinct

 queryset = self.filter_queryset(queryset.order_by('sku').distinct('sku'))

It depicts the raw like this,

4 A
1 C
2 B

Then now I want to sort this with id,

queryset = queryset.order_by('id')

However it shows error like this,

django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("defapp_log"."sku") "d...

DISTINCT ON( ... ) is not supported by MySQL, so I suppose you are using PostgreSQL. This is a non-standard feature implemented by PostgreSQL as an extension to SQL.

https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT says:

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

In other words, this is correct, because the sku column is the left-most expression in the ORDER BY clause.

SELECT DISTINCT ON (sku), id FROM ... ORDER BY sku, id;

But this is not correct:

SELECT DISTINCT ON (sku), id FROM ... ORDER BY id;

This does not satisfy the rule described in the documentation, because the sku column is not present in the ORDER BY clause.

Back to Top