PostgreSQL Selecting Most Recent Entry for a Given ID
There's about 5 different ways to do this, but here's one:
SELECT *FROM yourTable AS T1 WHERE NOT EXISTS( SELECT * FROM yourTable AS T2 WHERE T2.ID = T1.ID AND T2.Date > T1.Date)
And here's another:
SELECT T1.*FROM yourTable AS T1LEFT JOIN yourTable AS T2 ON( T2.ID = T1.ID AND T2.Date > T1.Date)WHERE T2.ID IS NULL
One more:
WITH T AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn FROM yourTable)SELECT * FROM T WHERE rn = 1
Ok, i'm getting carried away, here's the last one I'll post(for now):
WITH T AS ( SELECT ID, MAX(Date) AS latest_date FROM yourTable GROUP BY ID)SELECT yourTable.*FROM yourTableJOIN T ON T.ID = yourTable.ID AND T.latest_date = yourTable.Date
I would use DISTINCT ON
CREATE VIEW your_view ASSELECT DISTINCT ON (id) *FROM your_table aORDER BY id, date DESC;
This works because distinct on
suppresses rows with duplicates of the expression in parentheses. DESC
in order by
means the one that normally sorts last will be first, and therefor be the one that shows in the result.
https://www.postgresql.org/docs/10/static/sql-select.html#SQL-DISTINCT
This seems like a good use for correlated subqueries:
CREATE VIEW your_view ASSELECT *FROM your_table aWHERE date = ( SELECT MAX(date) FROM your_table b WHERE b.id = a.id)
Your date column would need to uniquely identify each row (like a TIMESTAMP
type).