First Row per Group in PostgreSQL

Written
  • 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)

Thanks for reading! If you have any questions or comments, please send me a note on Twitter.