How to add one more column to the same table while retaining the group by clause
P粉729436537
P粉729436537 2024-04-01 10:43:59
0
1
358

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

P粉729436537
P粉729436537

reply all(1)
P粉447785031

I think you can use ANY_VALUE(B_or_G)

for example:

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
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!