Django order_by() is not working along with distinct()

Question

How can i use order_by() in combination with distinct() ?

I am having multiple same run_id with different end_time and trying to filter distinct run_id and order by end_time

data = list(table_name.objects.filter(experience=experience)\
.values('run_id', 'end_time').distinct('run_id').order_by('-end_time'))

Following is the error :-

django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

However, the following ORM works fine without order_by but I want the latest end_time

data = list(table_name.objects.filter(experience=experience).values('run_id', 'end_time').distinct('run_id'))

Also see the *notes* in the [documentation for `distinct()`](https://docs.djangoproject.com/en/4.1/ref/models/querysets/#distinct). — djvg, Oct 25 '22 at 13:51

score 2 · Answer 1 · answered May 21 '21 at 16:25

2

The column in distinct() must also be in order_by()

data = list(table_name.objects.filter(experience=experience)\
.values('run_id', 'end_time').distinct('run_id').order_by('run_id', '-end_time'))

answered May 21 '21 at 16:25

lucutzu33

tried this, but this is not sorted by latest end_time, its kind of mixed – Mahesh May 21 '21 at 16:27
@Mahesh yes of course, because they are ordered by `run_id` first (which is necessary since you want to make a distinct on it). – Abdul Aziz Barkat May 21 '21 at 16:32
Thanks. this worked `data = list(table_name.objects.filter(experience=experience).values('run_id', 'end_time').distinct('end_time').order_by('end_time', 'run_id'))[::-1] ` – Mahesh May 21 '21 at 16:39

HudsonBarroso · Accepted Answer · 2021-05-21T16:34:35.257

1

What is database?

Try:

data = list(table_name.objects.filter(experience=experience)\
.values('run_id', 'end_time').distinct('run_id').order_by('run_id', '-end_time'))[::1]

edited May 21 '21 at 16:34

answered May 21 '21 at 16:30

HudsonBarroso

not working....i mean its not sorted by latest end_time – Mahesh May 21 '21 at 16:31
occur any error? data = list(table_name.objects.filter(experience=experience)\ .values('run_id', 'end_time').distinct('run_id').order_by('run_id', '-end_time'))[::1] – HudsonBarroso May 21 '21 at 16:33
There is no error, got distinct values of run_id and end_time, but sorted as per the latest end_time – Mahesh May 21 '21 at 16:34
This worked :) `data = list(table_name.objects.filter(experience=experience).values('run_id', 'end_time').distinct('end_time').order_by('end_time', 'run_id'))[::-1] ` – Mahesh May 21 '21 at 16:38
As mentioned in the [docs](https://docs.djangoproject.com/en/4.1/ref/models/querysets/#django.db.models.query.QuerySet.distinct), `distinct(*fields)` only works on PostrgreSQL. – djvg Oct 25 '22 at 13:58

2 Answers2