Question 377512 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to join table with lastest value from time series table

I have two tables in postgres.

CREATE TABLE data (
    name VARCHAR(10) PRIMARY KEY
);

CREATE TABLE timeseries (
    name VARCHAR(10) NOT NULL REFERENCES data(name),
    time TIMESTAMP NOT NULL,
    value INTEGER NOT NULL,

    PRIMARY KEY (name, time)
);

How I can join timeseries table to data table in such way I’ll get value column with latest timestamp

Tags:

2

Answers


  1. 0

    You can extract the latest records of timeseries table per name into a CTE using distinct on with custom order by and then join with data table (supposing that it has more columns and the above is just an illustration).

    with t as 
(
 select distinct on (name) *
 from timeseries order by name, time desc
)
select * -- your expression list here
from t join data using (name);
    Login or Signup to reply.
  2. 0

    The basic technique for this kind of tasks – use CTE with ROW_NUMBER window function:

    WITH d AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC) rn
    FROM timeseries
) SELECT * FROM d WHERE rn = 1;

    Within CTE we get all rows numbered according their timestamp in DESC order, so last row get row number = 1, after that we filter result of CTE for rows with number 1

    Test the solution here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search