Trying to find the max observation by group-CodePudding

I have the below code where I am trying to find a patients injury level and whether it is between 1 and 5 or 6 and 10. I have used the 'case when' clause below. The issue is, some patients have multiple injury levels. Is there a way for me to only get the max injury level and ignore the others?

SELECT DISTINCT 
    Name,
    PT_ID,
    InjuryDate,
    CASE 
        WHEN InjuryLevel BETWEEN 1 AND 5 
            THEN 1 
            ELSE 0 
    END AS Injury1to5,
    CASE 
        WHEN InjuryLevel BETWEEN 6 AND 10 
            THEN 1 
            ELSE 0 
    END AS Injury6to10,
    Cost AS totalpaid
FROM
    df1

CodePudding user response：

Assuming that you want the max injury level per patient and date:

SELECT DISTINCT
    Name,
    PT_ID,
    InjuryDate,
    CASE 
        WHEN MAX(InjuryLevel) BETWEEN 1 AND 5
            THEN 1 
            ELSE 0 
    END AS Injury1to5,
    CASE 
        WHEN MAX(InjuryLevel) BETWEEN 6 AND 10
            THEN 1 
            ELSE 0 
    END AS Injury6to10,
    Cost AS totalpaid
FROM
    df1
GROUP BY
    Name, PT_ID, InjuryDate

When you group, a column must either be listed in the GROUP BY clause or you must apply an aggregate function to it like MIN, MAX, SUM, etc.