Select for each field the one with the last timestamp value

Question

I have a table called "AGGREGATEVALUE" in a database schema "CLOUD". I'm using PostgreSQL:

CREATE TABLE CLOUD.AGGREGATEVALUE(
    id SERIAL,
    value CLOUD.FLOATVALUE NOT NULL,
    idBoard VARCHAR(32),
    idParameterRegolator VARCHAR(32),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    FOREIGN KEY(idBoard) REFERENCES CLOUD.BOARD(id),
    FOREIGN KEY(idParameterRegolator) REFERENCES CLOUD.PARAMETERREGOLATOR(id)
);

Here is an example of instance of this table (gained using a simple select for selecting all about idboard='board1')

id | value | idboard | idparameterregolator |         timestamp          
----+-------+---------+----------------------+----------------------------
  1 |  1.00 | board1  | param1               |       | 2014-08-24 10:29:33.677425
  2 |  2.00 | board1  | param1               |       | 2014-08-24 10:29:39.507868
  3 |  4.00 | board1  | param1               |       | 2014-08-24 10:29:46.286815
  4 |  5.00 | board1  | param1               |       | 2014-08-24 10:29:53.513363
  5 |  1.00 | board1  | param2               |       | 2014-08-24 10:29:58.252255
  6 |  2.00 | board1  | param2               |       | 2014-08-24 10:30:02.265151
  7 |  3.00 | board1  | param2               |       | 2014-08-24 10:30:05.480344
  8 |  4.00 | board1  | param2               |       | 2014-08-24 10:30:08.281917
  9 |  5.00 | board1  | param2               |       | 2014-08-24 10:30:10.845078
 10 |  1.00 | board1  | param3               |       | 2014-08-24 10:30:15.708794
 11 |  2.00 | board1  | param3               |       | 2014-08-24 10:30:18.82404
 12 |  3.00 | board1  | param3               |       | 2014-08-24 10:30:22.082542
 13 |  4.00 | board1  | param3               |       | 2014-08-24 10:30:24.662375
 14 |  5.00 | board1  | param3               |       | 2014-08-24 10:30:27.994689

Assuming by starting from the table above, what I need is to gain this other table:

value | idparameterregolator | timestamp
----------------------------------------------------------
5.00  | param1               | 2014-08-24 10:29:53.513363
5.00  | param2               | 2014-08-24 10:30:10.845078
5.00  | param3               | 2014-08-24 10:30:27.994689

That is, I need to gain for each parameter the corresponding value of the one with the last timestamp value.
I tried with ORDER BY clause but without any results.

tareq · Accepted Answer · 2014-08-24T10:39:16.807

1

distinct on with order by 2 values does the trick.

SELECT distinct on(value) * 
FROM   CLOUD.AGGREGATEVALUE 
ORDER  BY value, TIMESTAMP DESC;

edited Aug 24 '14 at 10:39

answered Aug 24 '14 at 10:32

tareq

1,119
6
14
28

Thank you! I modified the query in: `SELECT distinct on(idparameterregolator) * FROM CLOUD.AGGREGATEVALUE ORDER BY idparameterregolator, TIMESTAMP DESC;` and I obtain what I need! Thank you! – Selvaline Aug 24 '14 at 10:55
welcome. Whatever works for ya ;) – tareq Aug 24 '14 at 10:57

score 0 · Answer 2 · answered Aug 24 '14 at 16:45

Standard way, avoiding aggregates:

SELECT * FROM AGGREGATEVALUE av
WHERE NOT EXISTS (
    SELECT * FROM AGGREGATEVALUE nx
    WHERE nx.idparameterregolator =av.idparameterregolator
    AND nx.idboard = av.idboard
    AND nx.ztimestamp > av.ztimestamp
    );

Note: I replaced timestamp by ztimestamp since it is a reserved word.

Select for each field the one with the last timestamp value

2 Answers2