Django ORM Get first row for each group

Question

I want to be able to get the first row of each group of id & type, I'll give and example :

Type  Id   terms   data_signed   is_agreed
----------------------------------------
BUY   1     31      20-01-01         Y
BUY   1     12      19-01-01         Y
BUY   3     99      20-01-01         N
SELL  2     42      19-01-01         N
SELL  2     15      20-01-01         Y
SELL  2     87      20-01-01         Y

I want the result to be

Type  Id   terms   data_signed   is_agreed
----------------------------------------
BUY   1     31      20-01-01         Y
BUY   3     99      20-01-01         N
SELL  2     42      19-01-01         N

the records are already ordered by type->id I just want to fetch the first row of each type&field values

couldn't use distinct() since I'm not using PosgreSQL,

do you have any ideas?

sqlite, it seem like an easy task but I cant seem to solve this with django :( — Itai Bar, Oct 03 '22 at 21:16
i am a ltlle unsure what you are trying to achieve, that is a task for sqlite, django is imfo a framework for web, so it wouldn't do sql by it self, you could use dataframes, but stull sqllite is the way to go — nbk, Oct 03 '22 at 21:19
I just want to use Django methods to achieve what is described in the post. when using all_objects = all_objects.order_by('type', 'id').distinct('type', 'id') I'm getting an error — Itai Bar, Oct 03 '22 at 21:21
see https://docs.djangoproject.com/en/4.1/topics/db/sql/ there you can use https://stackoverflow.com/questions/751399/sql-query-how-to-apply-limit-within-group-by — nbk, Oct 03 '22 at 21:26
it should work on both SQLite (my unit test environment) and PostgreSQL, that's why I'm trying to do it with the abstraction of Django ORM — Itai Bar, Oct 03 '22 at 21:38
the code with row_number, will worl with sqllite and postgres, when the tables are identical — nbk, Oct 03 '22 at 21:43
Show your model. And your example could be SELL with Id= 1 for example? — inquirer, Oct 04 '22 at 07:43
What you want is theoretically impossible (although practically there could be some tricks). The reason it is impossible is that the request is ambiguous. You said you want the **first** row of each group but you don't give any ordering rule to determine the first row. This ordering rule must be deterministic (meaning the sorting key must be unique, otherwise your DB won't be able to figure out which one is the first one). It would be interesting to know the idea behind this query, because it looks a lot like an XY problem. What do you want to do with the query result *in fine*? — Antoine Pinsard, Oct 21 '22 at 14:40

Clepsyd · Accepted Answer · 2022-10-21T19:04:50.690

What's the desired order within each group? It's not consistent in your example. Without this it's impossible to determine which in the table is "the first":

However, if you can decide on a third field to order by, then you could use window functions which sqlite and postgresql both support. This example supposes that your model is Entry and the field by which to order each group is terms

from django.db.models import F, Min, Window

Entry.objects.annotate(
    lowest_terms=Window(
        expression=Min('terms'),
        partition_by=[F('type'), F('id')],
        order_by=F('terms')
    )
).filter(lowest_terms=F('terms'))

Cheeky and not really what window functions are designed for but that should work

Note: if your live environment uses postgres, you should really try to set up your dev environment with a postgres server too and use distinct()

Django ORM Get first row for each group

1 Answers1