Get the newest rows for each foreign key ID

Question

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

score 0 · Accepted Answer · answered Oct 25 '16 at 20:23

0

This is known as greatest-n-per-group, 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)

answered Oct 25 '16 at 20:23

univerio

19,548
3
66
68

Is this solution faster than the one I posted? – Colton Allen Oct 25 '16 at 20:28
@ColtonAllen In general, yes, slightly. See [this answer](http://stackoverflow.com/a/7630564/341730) for details. – univerio Oct 25 '16 at 20:35

score 0 · Answer 2 · answered Oct 25 '16 at 20:27

The solution is to left join an aliased model to itself (with a special join condition). Then filter out the rows that do not have an id.

model = Model
aliased = aliased(Model)
query = model.query.outerjoin(aliased, and_(
    aliased.primary_id == model.primary_id,
    aliased.created_at > model.created_at))
query = query.filter(aliased.id.is_(None))

Get the newest rows for each foreign key ID

2 Answers2