CODEWITHSUNDEEP
sqldatabasepostgresqlselectgreatest-n-per-group
BLuFeNiX
BLuFeNiX

Reputation: 2594

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

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?

Upvotes: 5

Views: 702

Answers (4)

dognose
dognose

Reputation: 20909

Simply join the table with itself, Add "<" as a join condition, and use the result where the right timestamp is null - then there is NO larger item for the defined 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 fiddle: http://sqlfiddle.com/#!15/39e7a/4

Upvotes: 1

comfortablydrei
comfortablydrei

Reputation: 316

Filter out everything but the newest identifier like this:

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

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659187

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?

Upvotes: 8

Lamak
Lamak

Reputation: 70678

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

Upvotes: 5

Related Questions