Column list in Table 1: Plan_ID, Claim_id, Patient_id, B_OR_G
Column list in Table 2: Ojid, Shapland
select distinct a.Plan_ID , a.Total_Claims , Total_Patients , b.PERIOD , b.ORGID,a.B_OR_G FROM (Select distinct Plan_ID , count(distinct Claim_id) as Total_Claims , count(distinct Patient_id) as Total_Patients from table1 group by 1) a JOIN (select * , row_number() over (partition by ORGID,SHAPLANID order by PROCESSINGDATE desc) as rank from table2 qualify rank = 1) b ON LTRIM(a.PLAN_ID, '0') = b.SHAPLANID
In the above query, I want to extract one more column named "B_or_G" from table1 (i.e. a) without interfering with the group by clause as it is necessary as per our requirements.
Is there a better way to do this? Thanks! !
I think you can use ANY_VALUE(B_or_G)
for example: