Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE values_tbl (
    request_id  int  ,
    city varchar(20),
    country varchar(5)
   );
    
    
INSERT INTO values_tbl values 
(1,'Berlin','DE'),
(1,'Dortmund','DE'),
(1,'Dresden','DE'),
(1,'Paris','FR'),
(1,'London','EN'),
(2,'Dublin','IR'),
(2,'Bochum','DE'),
(2,'Essen','DE'),
(2,'Herne','DE');


CREATE TABLE sys_Log (
    request_id  int  ,
    request_date date
   );
    
    
INSERT INTO sys_Log values 
(1,'2022-01-01'),
(2,'2022-01-02');


CREATE TABLE



INSERT 0 9



CREATE TABLE



INSERT 0 2


create view my_view as with cte as (
      SELECT request_id,city,country
             ,RANK () OVER (ORDER BY request_id desc ) id_rank
     FROM
     values_tbl
) select cte.request_id,city,country,request_date 
  from cte 
  inner join sys_Log on cte.request_id=sys_Log.request_id
  where cte.id_rank=1;

CREATE VIEW


select * from my_view;


request_id
city
country
request_date


2
Herne
DE
2022-01-02

2
Essen
DE
2022-01-02

2
Bochum
DE
2022-01-02

2
Dublin
IR
2022-01-02



SELECT 4

request_id	city	country	request_date
2	Herne	DE	2022-01-02
2	Essen	DE	2022-01-02
2	Bochum	DE	2022-01-02
2	Dublin	IR	2022-01-02