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

Question

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!!

if it is grouped by 1 you can select max or min of that column ? — VBoka, May 10 '22 at 08:38

score 1 · Accepted Answer · answered May 10 '22 at 08:47

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.

This definitely helped, Thanks Much!! Please confirm, Using ANY_VALUE( ) will give exact mapped results to the other columns right? @bubbak — User1011, May 10 '22 at 09:57

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

1 Answers1