Left join Subquery but limit to first row

Question

I am joining a metric table to a client listing. Currently when joined, the joined column populates for each client on all rows.

The goal is to only have the metric populate once per client grouping (hsp.PROV_ID). - SECOND IMAGE

select hsp.PROV_ID, HSP.id from  hsp_client hsp

    left join(select  vat.ASGN_PROV_ID, sum(vat.ASGN_DFI_CNT) as 'Deficiency Count', sum(vat.DLQ_DFI_CNT) as 'Delinquent Count' from V_DT_PROV_ASGN_METRICS vat 
where  VAT.PAT_CLASS_C IN ('101', '102','104') 
 and VAT.METRIC_DATE  = Convert(DATE, GetDate())
  --and DEF_ID IS NULL
   and vat.DEF_TYPE_C not in ('9')
group by vat.ASGN_PROV_ID,vat.ASGN_DFI_CNT,vat.DLQ_DFI_CNT
) vtt on vtt.ASGN_PROV_ID =hsp.PROV_ID

group by hsp.PROV_ID, hsp.id

Currently the Output Desired Output

Please don't post images of data; data is `text` and should be supplied as such. What is that data as well, you don't explain. — Thom A, Nov 17 '19 at 18:03
Possible duplicate of [SQL Server: How to Join to first row](https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row) — gbjbaanb, Nov 17 '19 at 18:05

score 1 · Accepted Answer · answered Nov 17 '19 at 18:02

If I understand your problem, you just want the metrics on the "first" row for each prov_id, where "first" row is defined by hsp.id.

You don't need aggregation in the outer query, just some logic on window functions:

select hsp.PROV_ID, HSP.id,
       (case when hsp.id = min(hsp.id) over (partition by hsp.prov_id) 
             then vtt.DeficiencyCount
        end) as DeficiencyCount,
       (case when hsp.id = min(hsp.id) over (partition by hsp.prov_id) 
             then vtt.DelinquentCount
        end),
from hsp_client hsp left join
     (select vat.ASGN_PROV_ID, sum(vat.ASGN_DFI_CNT) as DeficiencyCount,
             sum(vat.DLQ_DFI_CNT) as DelinquentCount
      from V_DT_PROV_ASGN_METRICS vat 
      where VAT.PAT_CLASS_C IN ('101', '102','104') and
            VAT.METRIC_DATE  = Convert(DATE, GetDate()) and
  --and DEF_ID IS NULL
            vat.DEF_TYPE_C not in ('9')
       group by vat.ASGN_PROV_ID
      ) vtt
      on vtt.ASGN_PROV_ID = hsp.PROV_ID
order by hsp.PROV_ID, hsp.id;

This logic is more normally done in the application layer, but you can do it in SQL.

I'm not even sure the aggregation is needed in the subquery. But . . . it should be only at the "prov id" level.

Left join Subquery but limit to first row

1 Answers1