I have two tables:
01_System_Log
request_date request_id 2022-01-01 1 2022-01-10 2 2022-01-20 3
Records
firm name city request_id firm_a John NY 1 firm_b Will LA 1 firm_c Andy SF 1 firm_c Marga CH 1 firm_a John NY 2 firm_b Will LA 2 firm_b Nancy LA 2 firm_c Andy SF 2 firm_c Marga CH 2 firm_a John NY 3 firm_b Will CH 3 firm_b Nancy LA 3 firm_c Andy SF 3 firm_c Marga SF 3 firm_c Joe SF 3
I need to create a view, where the records of the last request id (3) are compared with the previous (2). So far I made a view, where the most actual records are shown.
SELECT DISTINCT main.firm, main.name, main.city, sys_log.request_id, FROM "Records" main INNER JOIN ( SELECT sys.request_date, sys.request_id FROM "01_System_Log" sys ORDER BY sys.request_id DESC LIMIT 1 ) sys_log ON sys_log.request_id = main.request_id WHERE main.request_id IS NOT NULL ORDER BY main.firm;
I am not sure how to proceed, while in all compare examples, which I have found, only one table was used.
My ideal result is:
firm name_ city request_id firm_n name_n city_n request_id_n firm_a John NY 2 firm_a John NY 3 firm_b Will LA 2 firm_b Will CH 3 firm_b Nancy LA 2 firm_b Nancy LA 3 firm_c Andy SF 2 firm_c Andy SF 3 firm_c Marga CH 2 firm_c Marga SF 3 firm_c Joe SF 3
Advertisement
Answer
The information is all coming from one table "Records"
so you just need to include that table twice in your query, using appropriate aliases, once to get the records with the highest request_id
and once for the request_id
which is lower than the highest. Finally you need to find what the highest request_id
is, which you can do with a CTE on the "01_System_Log"
table.
The CTE is straightforward:
WITH highest AS ( SELECT request_id AS id FROM "01_System_Log" ORDER BY request_date DESC LIMIT 1 )
The records that use the highest request_id
are also easily found:
SELECT firm, name, city, request_id FROM "Records" JOIN highest ON "Records".request_id = highest.id
To get the records with the second highest request_id
for each combination of firm and name you need to use a window function:
SELECT DISTINCT firm, name, first_value(city) OVER w AS city, first_value(request_id) OVER w AS request_id FROM "Records" JOIN highest ON "Records".request_id < highest.id -- exclude the highest request_id WINDOW w AS (PARTITION BY (firm, name) ORDER BY request_id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
And then you tie it all together with sub-queries (and removing columns with duplicate information):
WITH highest AS ( SELECT request_id AS id FROM "01_System_Log" ORDER BY request_date DESC LIMIT 1 ) SELECT curr.firm, curr.name, prev.city AS previous_city, prev.request_id AS previous_request_id, curr.city AS current_city, curr.request_id AS current_request_id FROM ( SELECT DISTINCT firm, name, first_value(city) OVER w AS city, first_value(request_id) OVER w AS request_id FROM "Records" JOIN highest ON "Records".request_id < highest.id WINDOW w AS (PARTITION BY (firm, name) ORDER BY request_id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ) prev RIGHT JOIN ( SELECT firm, name, city, request_id FROM "Records" JOIN highest ON "Records".request_id = highest.id ) curr USING (firm, name) ORDER BY curr.firm;