Select only the most recent value for each identifier in a table
database, greatest-n-per-group, postgresql, select, sql
Solution
The simplest (and often fastest) way is `DISTINCT ON` in Postgres:
SELECT DISTINCT ON (identifier) *
FROM tbl
ORDER BY identifier, tstamp DESC;
This also returns an ordered list. SQLFiddle. Details: Select first row in each GROUP BY group?
Problem
I have a table that looks like this: ``` identifier | value | tstamp -----------+-------+--------------------- abc | 21 | 2014-01-05 05:24:31 xyz | 16 | 2014-01-11 03:32:04 sdf | 11 | 2014-02-06 07:04:24 qwe | 24 | 2014-02-14 02:12:07 abc | 23 | 2014-02-17 08:45:24 sdf | 15 | 2014-03-21 11:23:17 xyz | 19 | 2014-03-27 09:52:37 ``` I know how to get the most recent value for a single identifier: ``` select * from table where identifier = 'abc' order by tstamp desc limit 1; ``` But I want to get the most recent value for all identifiers. How can I do this?