Postgresql: Create View from 2 Tables, JOIN on the max values of same column-CodePudding

I have 2 tables

Sys_Log

request_id   request_date
----------------------------
1            2022-01-01
2            2022-01-02

Values

request_id   city      country
---------------------------------
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

I am trying to create a view where only the data from the last request (max request_id) will be displayed.

Ideal result

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

So far I made one where request id is matched in both tables

SELECT * FROM (SELECT DISTINCT city, 
    country, 
    sys_log.request_id,
    sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
    SELECT sys.request_date, 
    sys.request_id
    FROM public."Sys_Log" AS sys
) AS sys_log ON sys_log.request_id = VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

I am not sure how to proceed, with max(request_id) I am getting an error.

SELECT * FROM (SELECT DISTINCT city, 
    country, 
    sys_log.request_id,
    sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
    SELECT sys.request_date, 
    max(sys.request_id) as max_ri
    FROM public."Sys_Log" AS sys
) AS sys_log ON sys_log.max_ri= VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

Error:

Spalte »sys.request_date« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden

Like the sys.request_date has to be used in GROUP-BY or in an agg. function.

CodePudding user response：

As explained in this post you can use limit. So your query would look like this:

SELECT * FROM (SELECT DISTINCT city, 
    country, 
    sys_log.request_id,
    sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
    SELECT sys.request_date, 
    sys.request_id
    FROM public."Sys_Log" ORDER BY sys.request_id DESC LIMIT 1 AS sys
) AS sys_log ON sys_log.request_id = VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

CodePudding user response：

You can use the RANK function , try :

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;

Result:

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

Demo

View query:

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;

Demo