Select row where latest quarter and year In POSTGRESQL

January 25, 2022

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 .

>Solution :

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;