# Django ORM Group by issue

**URL:** <https://forum.djangoproject.com/t/django-orm-group-by-issue/9068>\
**Category:** Using Django\
**Created:** [August 2, 2021, 9:48pm UTC](https://forum.djangoproject.com/t/django-orm-group-by-issue/9068 "2021-08-02T21:48:23Z")\
**Posts on this page:** 4\
**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:** [August 2, 2021, 9:48pm UTC](https://forum.djangoproject.com/t/django-orm-group-by-issue/9068/1 "2021-08-02T21:48:23Z")

</div>

Hi

This might be the duplicated issue but it hasn’t been resolved yet.  
I have a model like this.

```auto
class Score(models.Model):
    ...

    id = models.BigAutoField(primary_key=True)
    venuemachine = models.ForeignKey(VenueMachine, related_name='scores', null=True,
            on_delete=models.CASCADE)
    ...
    player = models.ForeignKey(User, blank=True, null=True, on_delete=models.CASCADE,
            related_name='scores')
    score = models.BigIntegerField(blank=True, null=True)

```

And I am going to get top 20 scores on a venuemachine.  
But I want to get only one score per player.  
So those 20 scores are all from different players.

My queryset looks like this

```auto
scores = (
	Scores
		.objects
		.filter(venuemachine_id__in=vm_ids)
)

...

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

```

But I still get duplicate players.  
So I printed out my SQL query from QuerySet and it looks like this.

```auto
SELECT "venuemachine_score"."id", "venuemachine_score"."player_id", "venuemachine_score"."venuemachine_id", MAX("venuemachine_score"."score") AS "max_score", "venuemachine_score"."id" AS "player_score_id" 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

```

It still groups by `id` but it should group by `player_id`.  
I am not sure how I can solve this.  
This really drives me crazy.  
Any help would be appreciated.

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 2, 2021, 9:48pm UTC](https://forum.djangoproject.com/t/django-orm-group-by-issue/9068/2 "2021-08-02T21:48:52Z")

</div>

@KenWhitesell  
Could you please revisit this issue?

---

<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, 2:37am UTC](https://forum.djangoproject.com/t/django-orm-group-by-issue/9068/3 "2021-08-03T02:37:05Z")

</div>

Quite frankly, at this point, I’d throw out the idea of trying to do it in one query. I’d get as close as I could get with the query and use the final filtering / organization in code. My hunch is you’ve spent far more time trying to find the “optimal” solution than you will ever save over the lifetime of the application.

---

<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, 3:08am UTC](https://forum.djangoproject.com/t/django-orm-group-by-issue/9068/4 "2021-08-03T03:08:38Z")

</div>

I posted what I thought was a good basis for what you’re trying to do here at [Django queryset chain annotate and distinct together - #6 by KenWhitesell](https://forum.djangoproject.com/t/django-queryset-chain-annotate-and-distinct-together/8717/6). Is this along the right lines, or is there some difference between this and that that I’m not seeing?