I need help with my issue here . I need to select rows where quarter and year are latest . Here is example of my table Risk Master table named HD_Risk_Master:- Another table that join with HD_Risk_Master table named HD_Case_Resolution 1:M Expected Result My result of query , no HDS-OP3 and HDS-OP4 in my query which i realised the max

Select row where latest quarter and year In POSTGRESQL

I need help with my issue here . I need to select rows where quarter and year are latest . Here is example of my table

Risk Master table named HD_Risk_Master:-

rm_log_no     rm_company_id      
 HDS-OP1           004          
 HDS-OP2           004         
 HDS-OP3           004           
 HDS-OP4           004

Another table that join with HD_Risk_Master table named HD_Case_Resolution 1:M

    cr_log_no     cr_quarter          cr_year
     HDS-OP1           Q3              2021   
     HDS-OP1           Q4              2021   ->latest
     HDS-OP2           Q3              2021
     HDS-OP2           Q4              2021   ->latest
     HDS-OP3           Q4              2021
     HDS-OP3           Q1              2022   ->latest
     HDS-OP4           Q4              2021
     HDS-OP4           Q1              2022   ->latest

Expected Result

rm_log_no     rm_company_id      cr_log_no     cr_quarter          cr_year   
  HDS-OP1         004             HDS-OP1           Q4              2021   
  HDS-OP2         004             HDS-OP1           Q4              2021  
  HDS-OP3         004             HDS-OP1           Q1              2022   
  HDS-OP4         004             HDS-OP1           Q1              2022

My result of query , no HDS-OP3 and HDS-OP4 in my query which i realised the max year is 2022 and the max quarter is Q4 which in 2021 . It supposed to read year 2022 and quarter 1(in year 2022)

rm_log_no     rm_company_id      cr_log_no     cr_quarter          cr_year   
  HDS-OP1         004             HDS-OP1           Q4              2021   
  HDS-OP2         004             HDS-OP1           Q4              2021

Here is my attempt query which resulted as above ;-

select * from "HD_Risk_Master" as "risk" inner join "HD_Case_Resolution" as "reso" on "reso"."cr_log_no" = "risk"."rm_log_no" 
 inner join (SELECT cr_log_no,MAX(cr_year) as max_year
 FROM public."HD_Case_Resolution"
 GROUP BY cr_log_no) d on "reso"."cr_log_no" = "d"."cr_log_no" and "d"."max_year" = "reso"."cr_year"
 inner join (SELECT cr_log_no,MAX(cr_quarter) as max_quarter
 FROM public."HD_Case_Resolution" GROUP BY cr_log_no) c 
 on "reso"."cr_log_no" = "c"."cr_log_no" and "c"."max_quarter" = "reso"."cr_quarter"
 where "rm_company_id" in ('004')

Hope it’s help and sorry for my bad explaination and grammar .

Answer

Join master with a derived table which orders the rows from details

select m.*, r.cr_quarter, r.cr_year
from HD_Risk_Master m
left join (
   select *, row_number() over(partition by cr_log_no order by cr_year desc, cr_quarter desc) rn
   from HD_Case_Resolution
) r on r.rn=1 and r.cr_log_no = m.rm_log_no;

db<>fiddle

Advertisement

Answer