Select only the most recent value for each identifier in a table

Question

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?

Answer 1

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?

Answer 2

SELECT *
FROM (  SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY identifier 
                                  ORDER BY tstamp DESC)  AS RN
        FROM YourTable) AS T
WHERE RN = 1

Here is an sqlfiddle with a demo of this.

The results are:

╔════════════╦═══════╦═════════════════════════════════╦════╗
║ IDENTIFIER ║ VALUE ║             TSTAMP              ║ RN ║
╠════════════╬═══════╬═════════════════════════════════╬════╣
║ abc        ║    23 ║ February, 17 2014 08:45:24+0000 ║  1 ║
║ qwe        ║    24 ║ February, 14 2014 02:12:07+0000 ║  1 ║
║ sdf        ║    15 ║ March, 21 2014 11:23:17+0000    ║  1 ║
║ xyz        ║    19 ║ March, 27 2014 09:52:37+0000    ║  1 ║
╚════════════╩═══════╩═════════════════════════════════╩════╝