distinct¶

You can use distinct clauses with the following queries:

Select

>>> await Band.select(Band.name).distinct()
[{'title': 'Pythonistas'}]

This is equivalent to SELECT DISTINCT name FROM band in SQL.

on¶

Using the on parameter we can create DISTINCT ON queries.

Note

Postgres and CockroachDB only. For more info, see the Postgres docs.

If we have the following table:

class Album(Table):
    band = Varchar()
    title = Varchar()
    release_date = Date()

With this data in the database:

Albums¶
id	band	title	release_date
1	Pythonistas	Py album 2021	2021-12-01
2	Pythonistas	Py album 2022	2022-12-01
3	Rustaceans	Rusty album 2021	2021-12-01
4	Rustaceans	Rusty album 2022	2022-12-01

To get the latest album for each band, we can do so with a query like this:

>>> await Album.select().distinct(
...     on=[Album.band]
... ).order_by(
...     Album.band
... ).order_by(
...     Album.release_date,
...     ascending=False
... )

[
    {
        'id': 2,
        'band': 'Pythonistas',
        'title': 'Py album 2022',
        'release_date': '2022-12-01'
    },
    {
        'id': 4,
        'band': 'Rustaceans',
        'title': 'Rusty album 2022',
        'release_date': '2022-12-01'
    },
]

The first column specified in on must match the first column specified in order_by, otherwise a DistinctOnError will be raised.

Source¶

class piccolo.query.mixins.DistinctOnError¶: Raised when DISTINCT ON queries are malformed.