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?
UPDATE
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.
