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 send out interesting things I read and the occasional nature photo.

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