This question already has an answer here:
I have a table which roughly looks like this
uuid uuid, id id, data text, last_updated timestamp
Each row has an associated unique uuid. However, the key of interest is the id (indexed). Whenever we do an update, it happens that a row with the same id is inserted, the timestamp is the moment of putting it in the database, and it gets its own uuid. I would like to retrieve several of these rows in one query, but retrieving only the latest version.
So far I have
SELECT * FROM table WHERE id IN (16638051, 14727883) GROUP BY id, uuid;
(ids are examples). However, I'm curious how to efficiently retrieve only the rows with the latest timestamp in that query?
Or would doing a single row at a time and retrieving it as a transaction be better?
This seems to do the trick (from another question).
SELECT * FROM ( SELECT DISTINCT ON (id) * FROM table WHERE id IN (14727883, 16638051) ORDER BY id, last_updated DESC NULLS LAST ) sub ORDER BY last_updated DESC NULLS LAST
However, I wonder if a materialized view wouldn't be easier. It's a read heavy & low write database with a couple million rows, with the updates only happening daily.