Change order_by after using distinct in SQLAlchemy

Question

I'm trying to create a query that shows my results ordered by value 'difference_absolute', and is unique on 'company_id' (since multiple company_id's will be present and in the result I only like to display every company_id once). For this, I tried using ORDER_BY and DISTINCT:

db.session.query(Ranking).\
                 filter(Ranking.timestamp > last7days).\
                 group_by(Ranking.company_id, Ranking.id).\
                 distinct(Ranking.company_id).\
                 order_by(Ranking.difference_absolute).\
                 limit(10)

However, it turns out you can only do ORDER_BY on the item you use in DISTINCT, and I cannot figure out how to do this and after that change the order.

Any pointers?

Try what Laurent Meyer's comment suggests here: https://stackoverflow.com/a/43612364/16361. — AdamKG, Oct 28 '20 at 14:16

score 0 · Answer 1 · answered Oct 28 '20 at 15:30

Thanks @AdamKG for the pointer, I got it working with the following query:

Ranking.query.distinct(Ranking.company_id).\
                       filter(Ranking.timestamp > last7days).\
                       from_self().\
                       order_by(Ranking.difference_absolute.desc()).\
                       limit(10)

I will read up on from_self() a bit more.

Change order_by after using distinct in SQLAlchemy

1 Answers1