Max Price Using Multiple Tables and Columns The below code is working for me to pull out the max Avg Price, but I need to add another couple of fields from the dbo.TreatmentEvent table, when I add another column it stops giving me the max and instead just starts listing out all values.
SELECT Max_AvgPrice = MAX(pav.AvgPrice),
       te.a_ID_Treatment
  FROM dbo.TreatmentEvent           te
  LEFT JOIN 
       dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
  LEFT JOIN 
       dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
  LEFT JOIN 
       dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
 GROUP BY  
       te.a_ID_Treatment,
       YEAR(te.TreatmentEventStartDate),
       pca.a_IDPK_ProcedureCategory,
       te.a_ID_TreatmentEventStatus
HAVING (YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
        OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1)
       AND (pca.a_IDPK_ProcedureCategory = 1)
       AND (te.a_ID_TreatmentEventStatus = 3);

When I add in other columns this is an example the result;
a_ID_Treatment     a__IDPK_TreatmentEvent     Code_Insurance_1     Max_AvgPrice
454646             432023                     455                  1030
454646             431168                     194                  719


What I need to get is just the first line with the AvgPrice of 1030, but I need to see the other columns too that apply to that line.
Anyone have any suggestions?
Thanks
I'm guessing when you're adding the other columns, you are changing the GROUP BY as the columns in the SELECT must be included. That's going to change your results.
Instead, I do the joining and grouping necessary to get the results I wanted, in your case the average, in a sub query and join to that.
Try this:
SELECT
    Max_AvgPrice,
    te.a__IDPK_TreatmentEvent,
    te.Code_Insurance_1
FROM
    (
        SELECT
            Max_AvgPrice = MAX(pav.AvgPrice),
            te.a_ID_Treatment
        FROM 
            dbo.TreatmentEvent           te
        LEFT JOIN 
            dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
        LEFT JOIN 
            dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
        LEFT JOIN 
            dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
        GROUP BY  
            te.a_ID_Treatment,
            YEAR(te.TreatmentEventStartDate),
            pca.a_IDPK_ProcedureCategory,
            te.a_ID_TreatmentEventStatus
        HAVING
            (
                YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
                OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1
            )
            AND (pca.a_IDPK_ProcedureCategory = 1)
            AND (te.a_ID_TreatmentEventStatus = 3)
    ) AS subquery
    INNER JOIN dbo.TreatmentEvent AS te ON subquery.a_ID_Treatment = te.a_ID_Treatment