Select only the last value for each identifier in the 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 last value for a single identifier:

select * from table where identifier = 'abc' order by tstamp desc limit 1;

But I want to get the last value for all identifiers. How can i do this?

Answer 1

The easiest (and fastest) way is DISTINCT ONin Postgres:

SELECT DISTINCT ON (identifier) *
FROM   tbl
ORDER  BY identifier, tstamp DESC;

It also returns an ordered list.
SQLFiddle
Details:
Select the first row in each GROUP BY?

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 a sqlfiddle with a demonstration of this.

:

╔════════════╦═══════╦═════════════════════════════════╦════╗
║ 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 ║
╚════════════╩═══════╩═════════════════════════════════╩════╝

Answer 3

, :

select * from table t
where not exists 
    ( SELECT 1
      FROM table x
      WHERE x.identifier = t.identifier
        AND x.tstamp > t.tstamp
     ) ;

Answer 4

Just join the table with yourself, add "<" as the join condition, and use the result when the correct timestamp is zero - then there is no more significant element for the specified identifier)

 SELECT t1.* FROM tbl t1
   LEFT JOIN tbl t2
   ON t1.identifier = t2.identifier AND
    t1.tstamp < t2.tstamp
   WHERE t2.tstamp is null

Stolen Violin: http://sqlfiddle.com/#!15/39e7a/4