How to add one more column in same table while keeping the group by clause-CodePudding

List of columns in Table1: Plan_ID, Claim_id, Patient_id, B_OR_G

List of columns in Table2: ORGID, SHAPLANID

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 pull one more column named 'B_or_G' from table1 (i.e., a) but without disturbing the group by clause as it is necessary as per our requirement.

Is there any better way to do this? Thanks!!

CodePudding user response：

I think you can use ANY_VALUE(B_or_G)

For your excample:

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 
             , ANY_VALUE(B_OR_G)
      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

,hope it help.