Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (id int, date time, device int, value1 numeric);
	
INSERT INTO table1 VALUES
  (1, '10:22', 13, 0.53)
, (2, '10:24', 13, 0.67)
, (3, '10:25', 14, 0.83)
, (4, '10:25', 13, 0.32)
;

CREATE TABLE table2 (id int, date time, device int, value2 numeric);
	
INSERT INTO table2 VALUES
  (22, '10:18', 13, 0.77)
, (23, '10:21', 14, 0.53)
, (24, '10:23', 13, 0.67)
, (25, '10:28', 14, 0.83)
, (26, '10:31', 13, 0.23)
;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 5


-- DISTINCT ON
SELECT DISTINCT ON (t1.id)
       t1.id, t1.date, t1.device, t1.value1, t2.value2
FROM   table1 t1
JOIN   table2 t2 USING (device)
WHERE  t1.date > t2.date
ORDER  BY t1.id, t2.date DESC;


id
date
device
value1
value2


1
10:22:00
13
0.53
0.77

2
10:24:00
13
0.67
0.67

3
10:25:00
14
0.83
0.53

4
10:25:00
13
0.32
0.67



SELECT 4


-- JOIN LATERAL (requires pg 9.3+)
SELECT t1.id, t1.date, t1.device, t1.value1, t2.value2
FROM   table1 t1
LEFT   JOIN LATERAL (
   SELECT value2
   FROM   table2
   WHERE  device = t1.device
   AND    date   < t1.date
   ORDER  BY date DESC
   LIMIT  1
   ) t2 ON true;


id
date
device
value1
value2


1
10:22:00
13
0.53
0.77

2
10:24:00
13
0.67
0.67

3
10:25:00
14
0.83
0.53

4
10:25:00
13
0.32
0.67



SELECT 4


-- UNION ALL / window func
SELECT id, date, device, value1, value2
FROM  (
   SELECT id, date, device, value1
        , min(value2) OVER (PARTITION BY device, grp) AS value2
   FROM  (
      SELECT *
           , count(value2) OVER (PARTITION BY device ORDER BY date) AS grp
      FROM  (
         SELECT id, date, device, value1, NULL::numeric AS value2 
         FROM   table1

         UNION  ALL
         SELECT id, date, device, NULL::numeric AS value1, value2
         FROM   table2
         ) s1
      ) s2
   ) s3
WHERE  value1 IS NOT NULL
ORDER  BY date, id;


id
date
device
value1
value2


1
10:22:00
13
0.53
0.77

2
10:24:00
13
0.67
0.67

3
10:25:00
14
0.83
0.53

4
10:25:00
13
0.32
0.67



SELECT 4
id	date	device	value1	value2
1	10:22:00	13	0.53	0.77
2	10:24:00	13	0.67	0.67
3	10:25:00	14	0.83	0.53
4	10:25:00	13	0.32	0.67