Colton Allen Colton Allen - 1 month ago 9
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.

Model.query.order_by(Model.created_at.desc()).group_by(Model.foreign_key_id).all()
# column "model.id" must appear in the GROUP BY clause


And I've tried distinct.

Model.query.order_by(Model.created_at.desc()).distinct(Model.foreign_key_id).all()
# SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Answer

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)