First Row per Group in PostgreSQL

Written
  • 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. And if you enjoyed this, I also have a newsletter where I write about tech thoughts, interesting things I've read, and project updates each Thursday.

You can check out a recent issue, or enter your email below to subscribe.