[Solved]-How to find top-X highest values in column using Django Queryset without cutting off ties at the bottom?

by

20👍

✅

Of course you can do it in one SQL query. Generating this query using django ORM is also easily achievable.

top_scores = (myModel.objects
                     .order_by('-score')
                     .values_list('score', flat=True)
                     .distinct())
top_records = (myModel.objects
                      .order_by('-score')
                      .filter(score__in=top_scores[:10]))

This should generate single SQL query (with subquery).

👤Krzysztof Szularz

2👍

As an alternative, you can also do it with two SQL queries, what might be faster with some databases than the single SQL query approach (IN operation is usually more expensive than comparing):

myModel.objects.filter(
    score__gte=myModel.objects.order_by('-score')[9].score
)

Also while doing this, you should really have an index on score field (especially when talking about millions of records):

class myModel(models.Model):
    name = models.CharField(max_length=255, unique=True)
    score = models.FloatField(db_index=True)
👤Michal Čihař

0👍

As an alternative to @KrzysztofSzularz’s answer, you can use raw sql to do this too.

There are 2 SQL operations to get what you want

SELECT score from my_application_mymodel order by score desc limit 10;

Above sql will return top 10 scores (limit does this)

SELECT name, score from my_application_mymodel where score in (***) order by score desc;

That will return you all the results whom score value is within the first query result.

SELECT name, score from my_application_mymodel where score in (SELECT score from my_application_mymodel order by score desc limit 10) order by score desc;

You can use Raw Queries, but probably you will got error messages while you try to run this. So using custom queries is the best

from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT name, score from my_application_mymodel where score in (SELECT score from my_application_mymodel order by score desc limit 10) order by score desc;")
return cursor.fetchall()

That will return you a list:

[("somename", 100.9),
 ("someothername", 99.9)
...
]

Django names tables according to your django model name (all lowercase) and application name in which your model lives under and join these to with an underscore. Like my_application_mymodel

👤Mp0int

Leave a comment