First Row per Group in PostgreSQL
- With Postgres you can use
DISTINCT ONinstead of
GROUP BYand 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.