Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


create table dbKey (
 id serial ,
 name text
);
create table dbData (
 id serial ,
 key_id int ,
 timestamp int 
);

CREATE TABLE



CREATE TABLE


insert into dbKey(name)
values 
('hello'),
('world');
insert into dbData(key_id, timestamp)
values 
(1, 100),
(2, 100),
(1, 101),
(2, 102),
(2, 102);

INSERT 0 2



INSERT 0 5


select * from dbKey;


id
name


1
hello

2
world



SELECT 2


select * from dbData;


id
key_id
timestamp


1
1
100

2
2
100

3
1
101

4
2
102

5
2
102



SELECT 5


WITH map AS (
  SELECT 
  DISTINCT(key_id),
  timestamp
  FROM (
      SELECT
         key_id,
         timestamp,
         RANK() OVER (PARTITION BY key_id ORDER by timestamp DESC) as rank   
      FROM dbData WHERE "timestamp" <= 102
  ) sub
  WHERE rank = 1
)
SELECT
  dbKey.*,
  dbData.*
  FROM 
  dbKey
  INNER JOIN map m ON dbKey.id = m.key_id
  INNER JOIN dbData ON m.key_id = dbData.key_id AND m.timestamp = dbData.timestamp;


id
name
id
key_id
timestamp


1
hello
3
1
101

2
world
5
2
102

2
world
4
2
102



SELECT 3