order_by

You can use order_by clauses with the following queries:

• Select

• Objects

To order the results by a certain column (ascending):

await Band.select().order_by(
    Band.name
)

To order by descending:

await Band.select().order_by(
    Band.name,
    ascending=False
)

You can specify the column name as a string if you prefer:

await Band.select().order_by(
    'name'
)

You can order by multiple columns, and even use joins:

await Band.select().order_by(
    Band.name,
    Band.manager.name
)

---

Advanced

Ascending and descending

If you want to order by multiple columns, with some ascending, and some descending, then you can do so using multiple order_by statements:

await Band.select().order_by(
    Band.name,
).order_by(
    Band.popularity,
    ascending=False
)

Advanced

SQL’s ORDER BY clause is surprisingly rich in functionality, and there may be situations where you want to specify the ORDER BY explicitly using SQL. To do this use QueryString.

In the example below, we are ordering the results randomly:

from piccolo.querystring import QueryString

await Band.select(Band.name).order_by(
    QueryString('random()'),
)

The above is equivalent to the following SQL:

SELECT "band"."name" FROM band ORDER BY random() ASC

Note

We used to use OrderByRaw for this (which still works for backwards compatibility).