Colton Allen Colton Allen - 11 months ago 54
Python Question

Get the newest rows for each foreign key ID

I don't want to aggregate any columns. I just want the newest row for each foreign key in a table.

I've tried grouping.

# column "" must appear in the GROUP BY clause

And I've tried distinct.

# SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Answer Source

This is known as , and for PostgreSQL you can use DISTINCT ON, as in your second example:

SELECT DISTINCT ON (foreign_key_id) * FROM model ORDER BY foreign_key_id, created_at DESC;

In your attempt, you were missing the DISTINCT ON column in your ORDER BY list, so all you had to do was:

Model.query.order_by(Model.foreign_key_id, Model.created_at.desc()).distinct(Model.foreign_key_id)