Get top rows by one column Django

I'm making a job to categorize earnings and expenses, from app of movimentations. To this, i need get the category to tile, with more cases.

For example, in this Scenario:

| title | category       | count |
| ----- | -------------- | ----- |
| Pizza | food           | 6     |
| Pizza | others_expense | 1     |
| Pizza | refund         | 1     |

I want return just the first row, because the title is the same, and category food is used with most frequency.

Code example

I want get the result using just Django ORM, because i have diferent databases and is more fast than iterate over a large list.

Model:
class Movimentation(models.Model):
    title = models.CharField(max_length=50)
    value = models.FloatField()
    category = models.CharField(max_length=50)
Consult:

My actual consult in Django ORM is.

Movimentation.objects \
    .values('title', 'category') \
    .annotate(count=Count('*')).order_by('title', '-count')
Result:
[
    {'title': 'Pizza', 'category': 'food', 'count': 6},
    {'title': 'Pizza', 'category': 'others_expense', 'count': 1},
    {'title': 'Pizza', 'category': 'refund', 'count': 1},
    {'title': 'Hamburguer', 'category': 'food', 'count': 1},
    {'title': 'Clothing', 'category': 'personal', 'count': 18},
    {'title': 'Clothing', 'category': 'home', 'count': 15},
    {'title': 'Clothing', 'category': 'others_expense', 'count': 1}
]
Expected result:

In this case, i get just one row by title, with the most used category.

[
    {'title': 'Pizza', 'category': 'food', 'count': 6},
    {'title': 'Hamburguer', 'category': 'food', 'count': 1},
    {'title': 'Clothing', 'category': 'personal', 'count': 18}
]
Back to Top