Django order_by() filter with distinct()

Question

How can I make an order_by like this ....

p = Product.objects.filter(vendornumber='403516006')\
                   .order_by('-created').distinct('vendor__name')

The problem is that I have multiple vendors with the same name, and I only want the latest product by the vendor ..

Hope it makes sense?

I got this DB error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON ("search_vendor"."name") "search_product"...

score 93 · Accepted Answer · edited May 23 '17 at 12:09

93

Based on your error message and this other question, it seems to me this would fix it:

p = Product.objects.filter(vendornumber='403516006')\
               .order_by('vendor__name', '-created').distinct('vendor__name')

That is, it seems that the DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So by making the column you use in distinct as the first column in the order_by, I think it should work.

edited May 23 '17 at 12:09

Community

1
1

answered Dec 14 '13 at 12:22

janos

120,954
29
226
236

34

But with this method it will first order by vendor name and only use "created" as a secondary ordering. Which means the order by "created" will be incorrect. https://docs.djangoproject.com/en/1.10/ref/models/querysets/#order-by – Ronen Ness Mar 28 '17 at 10:43
1

@Ness What you say is true. But not necessarily relevant. It seems the OP was not interested in ordering by `created`. He used `created` not for the purpose of ordering the final result, but for getting the correct record per vendor. It seems the ordering of the vendors was not important in the use case of the OP. In fact we don't know the true intended ordering, it's essentially unspecified by OP. – janos Mar 28 '17 at 11:06
Thank you, really not an obvious thing. – abcdn Feb 26 '18 at 16:14
24

what if I want to order by something else but have distinct values in another column? – EralpB Nov 30 '18 at 08:43
Thank you! You made my day. – May 31 '21 at 20:43

score 31 · Answer 2 · edited Jun 03 '20 at 15:09

31

Just matching leftmost order_by() arg and distinct() did not work for me, producing the same error (Django 1.8.7 bug or a feature)?

qs.order_by('project').distinct('project')

however it worked when I changed to:

qs.order_by('project__id').distinct('project')

and I do not even have multiple order_by args.

edited Jun 03 '20 at 15:09

Jace Browning

11,699
10
66
90

answered Nov 25 '15 at 14:48

Dmitriy Sintsov

3,821
32
20

1

This was the only thing that worked for me running Django 2.0.2 and PostgreSQL 10.1. I tried the other suggestions involving matching the order of arguments for `order_by` and `distinct` and those did not solve the issue. – Jules Feb 28 '18 at 08:10
1

Same issue here, not sure why _id was needed – dowjones123 Aug 14 '19 at 20:03
I also had success with adding __id to the order_by() but not the distinct() – Soundtemple Aug 17 '20 at 01:33
3

@dowjones123 The reason may be the default ordering defined on the related model (Project in this answer). The documentation [mentions this case](https://docs.djangoproject.com/en/dev/ref/models/querysets/#distinct) in the last Note in 'distinct()' description. – natka_m Aug 17 '20 at 10:07
In the original question `.order_by('-created')` is likely a datetime-like field. This answer doesn't help to sort by such a field, then get distinct. Clearly useful for other scenarios, but not for helpful for the specific question asked. – Jarad Dec 15 '22 at 06:13

kophygiddie · Answer 3 · 2019-08-15T21:55:22.903

27

In case you are hoping to use a separate field for distinct and order by another field you can use the below code

from django.db.models import Subquery

Model.objects.filter(
    pk__in=Subquery(
       Model.objects.all().distinct('foo').values('pk')
    )
).order_by('bar')

edited Aug 15 '19 at 21:55

answered May 27 '19 at 23:50

kophygiddie

1,232
13
15

This doesn't work properly with prefetch related as far as I can tell... – Kevin Parker Nov 27 '19 at 20:57
2

The other answers didn't work for me because I needed to perform ordering on other fields at a later time from that same queryset. This one worked. – L. B. Sep 17 '20 at 20:35
This worked for me - but needed order_by ... Model.objects.all().order_by('foo').distinct('foo').values('pk') ... – Piet van Leeuwen Aug 05 '21 at 23:15

score 4 · Answer 4 · edited May 23 '17 at 12:17

I had a similar issue but then with related fields. With just adding the related field in distinct(), I didn't get the right results.

I wanted to sort by room__name keeping the person (linked to residency ) unique. Repeating the related field as per the below fixed my issue:

.order_by('room__name', 'residency__person', ).distinct('room__name', 'residency__person')

Django order_by() filter with distinct()

4 Answers4

Linked