# Django queryset chain annotate and distinct together

**URL:** <https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717>\
**Category:** Using Django\
**Created:** [July 9, 2021, 5:42pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717 "2021-07-09T17:42:26Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [July 9, 2021, 5:42pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/1 "2021-07-09T17:42:26Z")

</div>

Hi!

I have queryset which uses both annotate and distinct but it throws the error.  
`'Error: annotate() + distinct(fields) is not implemented.'`

The code looks like this.

```auto
toys = (
    Toy
    .objects
    .values('owner', 'mall_id')
    .annotate(max_price=Max('price'))
    .order_by('-max_price', 'owner')
    .distinct('owner')[:30]
)

```

I want to get top expensive toys but only one per owner.

How can I get this done?

Thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 9, 2021, 7:46pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/2 "2021-07-09T19:46:17Z")

</div>

try:  
`toys = Toy.objects.distinct('owner').order_by('owner', '-price')`

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 9, 2021, 7:53pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/3 "2021-07-09T19:53:50Z")

</div>

Sorry, misinterpreted the original objective of the query.

To make sure I’m straight - you want the 30 most expensive toys, but if the same owner appears in that list, they aren’t included.

So, if you have the situation with the following rows in toys, and assuming you’re only looking for 3 entries instead of 30:

```auto
Owner Price
Joe $100
Jane $70
Jane $50
Fred $40
Mary $20

```

The list you’re looking for would look like this:

```auto
Owner Price
Joe $100
Jane $70
Fred $40

```

So if the top 100 priced items are all owned by the same person, the second item on the list would be the 101st-most expensive item.

Is this the correct interpretation?

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [July 9, 2021, 10:36pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/4 "2021-07-09T22:36:39Z")

</div>

Yes, that is correct interpretation

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 9, 2021, 11:52pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/5 "2021-07-09T23:52:55Z")

</div>

Ok, so I was on the right track:

> [@KenWhitesell](#):
>
> toys = Toy.objects.distinct(‘owner’).order\_by(‘owner’, ‘-price’)

The issue here is that it’s sorted by owner and not by price, so truncating the list gets the first “n” alphabetically and not by price, and sorting by price first doesn’t work because `distinct` expressions must match `order by` expressions.

I’m going to keep looking at this…

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 10, 2021, 12:59am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/6 "2021-07-10T00:59:55Z")

</div>

I think I’ve got something working here by using a Subquery:

```auto
toy_sub = Toys.objects.filter(owner=OuterRef('owner')).order_by('-price').values('price').annotate(max_price=Max('price')).values('max_price')[:1]
toys = Toys.objects.annotate(max_price=Subquery(toy_sub)).distinct('owner', 'max_price').order_by('-max_price', 'owner')

```

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [July 12, 2021, 5:21pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/7 "2021-07-12T17:21:41Z")

</div>

Hi @KenWhitesell

I will try this.

Will keep you posted

Thanks

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [August 3, 2021, 5:59am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/8 "2021-08-03T05:59:39Z")

</div>

@KenWhitesell  
That did not solve the problem.

```auto
sub_query = (
    scores
    .filter(player=OuterRef('player'))
    .order_by('-score')
    .values('score')
    .annotate(max_score=Max('score'))
    .values('max_score')[:1]
)

```

This gives the following error.

```auto
*** ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

```

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [August 3, 2021, 6:02am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/9 "2021-08-03T06:02:29Z")

</div>

So here’s the original QuerySet

```auto
scores = (
    scores
    .values('player', 'venuemachine_id')
    .annotate(max_score=Max('score'))
    .order_by('-max_score')[:count]
)

```

It worked well, grouped by `player_id` and ordered by `max_score`  
Then I needed to get `id` of scores, so I added `id` to `values`

```auto
scores = (
    scores
    .values('id', 'player', 'venuemachine_id')
    .annotate(max_score=Max('score'))
    .order_by('-max_score')[:count]
)

```

Then it broke grouping by `id` instead of `player_id`.

I hope this explains a bit more.

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [August 3, 2021, 6:10am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/10 "2021-08-03T06:10:16Z")

</div>

FYI

This is the raw SQL query of original QuerySet which does not contain `id` field.

```auto
SELECT "venuemachine_score"."player_id", "venuemachine_score"."venuemachine_id", MAX("venuemachine_score"."score") AS "max_score" FROM "venuemachine_score" WHERE ("venuemachine_score"."is_shared" = True AND "venuemachine_score"."venuemachine_id" IN (SELECT U0."id" FROM "venuemachine_venuemachine" U0 WHERE U0."machine_id" = 1195)) GROUP BY "venuemachine_score"."player_id", "venuemachine_score"."venuemachine_id" ORDER BY "max_score" DESC LIMIT 20

```

This is SQL Query of QuerySet that contains `id` field

```auto
SELECT "venuemachine_score"."id", "venuemachine_score"."player_id", "venuemachine_score"."venuemachine_id", MAX("venuemachine_score"."score") AS "max_score" FROM "venuemachine_score" WHERE ("venuemachine_score"."is_shared" = True AND "venuemachine_score"."venuemachine_id" IN (SELECT U0."id" FROM "venuemachine_venuemachine" U0 WHERE U0."machine_id" = 1195)) GROUP BY "venuemachine_score"."id" ORDER BY "max_score" DESC LIMIT 20

```

Please notice that difference of Group By clause.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 3, 2021, 12:36pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/11 "2021-08-03T12:36:39Z")

</div>

Correct, the actual query is _both_ lines. You can’t execute the first line alone as a query. I do not see where you’ve tried to use what I supplied.

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [August 3, 2021, 10:18pm UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/12 "2021-08-03T22:18:11Z")

</div>

I tried that first but it didn’t work, so I tried other stuffs

---

<div class="post-metadata">

**Author:** ![awtimmering](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/awtimmering/32/4109_2.png) [@awtimmering](https://forum.djangoproject.com/u/awtimmering)\
**Post date:** [August 4, 2021, 2:38am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/13 "2021-08-04T02:38:44Z")

</div>

I unfortunately don’t have time right now to mock this up in Django ORM, but the way you would probably normally approach this in SQL would be: (assuming I’m understanding what you’re trying to do correctly, based on [Ken’s comment](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/3):

1. Annotate to add a `RANK()` (or rather a `ROW_NUMBER()` to avoid duplicates) by descending price, partitioned by owner, let’s say we call this `owner_price_rank`
2. Then filter the dataset on `owner_price_rank == 1` (i.e. the most expensive item by each owner)
3. Then `ORDER BY price DESC`, and select the first 30 rows

For example:

```sql
WITH 
  OwnerRanking AS (
    SELECT
      mall_id,
      owner,
      price,
      ROW_NUMBER() OVER (PARTITION BY owner ORDER BY price DESC) AS owner_price_rank
    FROM
      sometable
  )
SELECT
  mall_id,
  owner,
  price
FROM OwnerRanking
WHERE
  owner_price_rank = 1
ORDER BY price DESC
LIMIT 30;

```

---

<div class="post-metadata">

**Author:** ![awtimmering](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/awtimmering/32/4109_2.png) [@awtimmering](https://forum.djangoproject.com/u/awtimmering)\
**Post date:** [August 4, 2021, 6:41am UTC](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/14 "2021-08-04T06:41:52Z")

</div>

Thought I’d give it a shot. While this is the ‘normal’ way to get to it in SQL, Django ORM currently doesn’t allow filters on top of Windows, see [ticket/28333](https://code.djangoproject.com/ticket/28333).

If/when addressed you _should_ be able to do something like below, but alas not yet … Back to plain SQL? 🙂

```auto
  qs = (
    models.Toy.objects
      .annotate(
        owner_price_rank=Window(
          expression=RowNumber(),
          partition_by=[F('owner')],
          order_by=F('price').desc()))
      .filter(owner_price_rank=1)
    [:30]
  )

```