PostgreSQL getting the lastest item from a history table (the entry with the latest timestamp)

SELECT * FROM history_items
WHERE timeofhistory=(SELECT MAX(timeofhistory) FROM history_items)

The sub query will fetch the highest timestamp and then that is used as a condition for the query.

note: If you are using a version older than 8.1, then MIN and MAX is not able to use indexes, so instead use a ORDER BY and LIMIT clause

SELECT * FROM history_items
ORDER BY timeofhistory DESC LIMIT 1

This will of course work for versions 8.1 and higher as well, it should be identical to MAX and MIN in time.

Tags: ,

Comments are closed.