First Row per Group in PostgreSQL

Written 2020-11-05

SQL
With Postgres you can use DISTINCT ON instead of GROUP BY and order by the other fields you want as well.

select distinct on (model_id) *
from reactive_data.models
order by model_id, major desc, minor desc

To speed it up, create an ordered index with the same fields used in the order by:
- CREATE INDEX ON table (model_id, major desc, minor desc)