How can I use ORDER BY descending in a SQLAlchemy query like the following?

This query works, but returns them in ascending order:

query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.id) .order_by(model.Entry.amount) # This row :) )

If I try:

.order_by(desc(model.Entry.amount))

then I get: NameError: global name 'desc' is not defined.

6 Answers

Just as an FYI, you can also specify those things as column attributes. For instance, I might have done:

.order_by(model.Entry.amount.desc())

This is handy since it avoids an import, and you can use it on other places such as in a relation definition, etc.

For more information, you can refer this SQLAlchemy 1.4 Documentation

from sqlalchemy import desc someselect.order_by(desc(table1.mycol))

Usage from @jpmc26

One other thing you might do is:

.order_by("name desc")

This will result in: ORDER BY name desc. The disadvantage here is the explicit column name used in order by.

You can use `.desc()` function in your query just like this

query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.id) .order_by(model.Entry.amount.desc()) )

This will order by amount in descending order or

query = session.query( model.Entry ).join( model.ClassificationItem ).join( model.EnumerationValue ).filter_by( id=c.row.id ).order_by( model.Entry.amount.desc() ) )

Use of desc function of SQLAlchemy

from sqlalchemy import desc query = session.query( model.Entry ).join( model.ClassificationItem ).join( model.EnumerationValue ).filter_by( id=c.row.id ).order_by( desc(model.Entry.amount) ) )

For official docs please use the link or check below snippet

sqlalchemy.sql.expression.desc(column) Produce a descending ORDER BY clause element.

e.g.:
from sqlalchemy import desc stmt = select([users_table]).order_by(desc(users_table.c.name)) 
will produce SQL as:
SELECT id, name FROM user ORDER BY name DESC 
The desc() function is a standalone version of the ColumnElement.desc() method available on all SQL expressions, e.g.:
stmt = select([users_table]).order_by(users_table.c.name.desc()) 
Parameters column – A ColumnElement (e.g. scalar SQL expression) with which to apply the desc() operation.

See also

asc()

nullsfirst()

nullslast()

Select.order_by()

You can try: .order_by(ClientTotal.id.desc())

session = Session() auth_client_name = 'client3' result_by_auth_client = session.query(ClientTotal).filter(ClientTotal.client == auth_client_name).order_by(ClientTotal.id.desc()).all() for rbac in result_by_auth_client: print(rbac.id) session.close()

Complementary at @Radu answer, As in SQL, you can add the table name in the parameter if you have many table with the same attribute.