Getting the primacy function for an emolyee

I have this following query to get the primacy for an employee :

with

employeeScopeFunctions as (

    select      e.employeeId, 
                es.FunctionId,
                ef.Label,
                c.CompanyName
                ,es.SortOrder

    from        employee e 
    LEFT JOIN       employee_scope es on es.employeeId = e.employeeId
    LEFT JOIN         employee_function ef on es.FunctionId = ef.FunctionId
    LEFT JOIN         Company c ON es.CompanyId = c.ID
    WHERE e.EmployeeId=54

)


SELECT DISTINCT esf.EmployeeId, esf.FunctionId



,STUFF((SELECT ',' + CompanyName 
        FROM employeeScopeFunctions es
        WHERE esf.EmployeeId=es.EmployeeId AND esf.FunctionId=es.FunctionId

                        FOR XML PATH('')),1,1,'') AS Companies
        ,SUM(esf.SortOrder) as sumOrder

                        FROM employeeScopeFunctions esf
                        GROUP BY esf.EmployeeId,esf.FunctionId

My output is like below :

EmployeeId FunctionId Label                                   CompanyName  sumOrder
54         39         Director                                C1,C2,X5     224
54         273        Group Chief Executive Officer           X6,F6        66
54         897         Group Regional Chief Executive Officer  VY,G7       130

What I want is to get primacy (primary,secondary, tertiary) to each function like below :

EmployeeId FunctionId Label                                   CompanyName  sumOrder primacy
54         39         Director                                C1,C2,X5     224      tertiary
54         273        Group Chief Executive Officer           X6,F6        66       primary
54         897         Group Regional Chief Executive Officer  VY,G7       130      secondary

The function having the minimal sumOrder will be the primary function and so on.

Answer

You can use a ROW_NUMBER() ordered by the SUM() result, then a CASE to display your custom string value.

;with employeeScopeFunctions as 
(
    SELECT
        e.employeeId, 
        es.FunctionId,
        ef.Label,
        c.CompanyName,
        es.SortOrder
    FROM
        employee e 
        LEFT JOIN employee_scope es on es.employeeId = e.employeeId
        LEFT JOIN employee_function ef on es.FunctionId = ef.FunctionId
        LEFT JOIN Company c ON es.CompanyId = c.ID
    WHERE 
        e.EmployeeId = 54
),
RankingBySum AS
(
    SELECT 
        esf.EmployeeId, 
        esf.FunctionId,
        STUFF (
            (
                SELECT ',' + CompanyName 
                FROM employeeScopeFunctions es
                WHERE esf.EmployeeId=es.EmployeeId AND esf.FunctionId=es.FunctionId
                FOR XML PATH('')
            )
            , 1,1,'') AS Companies,
        SUM(esf.SortOrder) as sumOrder,

        -- Add this ranking here
        SumRanking = ROW_NUMBER() OVER (PARTITION BY esf.EmployeeId ORDER BY SUM(esf.SortOrder) ASC)
    FROM 
        employeeScopeFunctions esf
    GROUP BY 
        esf.EmployeeId,
        esf.FunctionId
)
SELECT
    R.EmployeeId,
    R.FunctionId,
    R.Companies,
    R.sumOrder,

    -- Display your primacy condition here
    primacy = CASE R.SumRanking
        WHEN 1 THEN 'primary'
        WHEN 2 THEN 'seconday'
        WHEN 3 THEN 'tertiary' END
FROM
    RankingBySum AS R