Select row where latest quarter and year In POSTGRESQL-CodePudding

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 .

CodePudding user response：

You can do it like that (Result here)

with x as (
select row_number() over (partition by cr_log_no order by cr_year desc,cr_quarter desc) as row_line, * from hd_case_resolution
)
select * from x 
where x.row_line = 1
order by cr_log_no;

CodePudding user response：

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

CodePudding user response：

This would suffice:

select *,rank() over (partition by cr_quarter order by CASE cr_quarter 
      WHEN 'Q4' THEN 1
      WHEN 'Q3' THEN 2
      WHEN 'Q2' THEN 3
      WHEN 'Q1' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END ) as rn2  
, from (select *,rank() over (partition by cr_year order by cr_year desc ) as rn  from 
HD_Risk_Master a,HD_Case_Resolution  b
where
a.rm_log_no=n.cr_log_no) where rn=1 and rn2=1;