How to get the primacy of a function for an employee?

I have this following query to get all the functions and the related scopes (companies) for an employee :

SELECT * FROM employee_scope WHERE EmployeeId=54

Output :

  EmployeeId  FunctionId   CompanyId    SortOrder
    54          273          8            1
    54          273          19           2
    54          273          32           3
    54          273          151          4
    54          897          49           5
    54          897          77           6
    54          897          54           7
    54          333          56           8         
    54          333          90           9        
    54          123          78           10         

The logic behing sortOrder is / the minimum value gets the main function when an Employee has one primary function for one company. In this case EmployeeId=54 has the same main functionId=273 for different companies (scopes) with CompanyId=8,19,32,151

I want to get the primacy of each function as below :

    EmployeeId  FunctionId   CompanyId    SortOrder  Primacy
    54          273          8            1          Primary
    54          273          19           2          Primary
    54          273          32           3          Primary
    54          273          151          4          Primary
    54          897          49           5          Secondary
    54          897          77           6          Secondary
    54          897          54           7          Secondary
    54          333          56           8          Third
    54          333          90           9          Third
    54          123          78           10         Fourth

Answer

You can use window functions to compute both SortOrder and Primacy columns, like so:

select 
    EmployeeId,
    FunctionId,
    CompanyId,
    row_number() over(order by minCompanyId, CompanyId) SortOrder,
    case dense_rank() over(order by minCompanyId)
        when 1 then 'Primacy'
        when 2 then 'Secondary'
        when 3 then 'Third'
        when 4 then 'Fourth'
    end Primacy
from (
    select
        t.*,
        min(CompanyId) over(partition by EmployeeId, FunctionId) minCompanyId
    from mytable t
) t

The inner query computes the smallest company id per function id. Then, the outer query uses that information to generate the sequence number, and to rank the groups. Note that the sort order is computed by the query – you should not need (or want) to store this derived information: if a new record is inserted, you might need to reassign new values on the entire column.

Demo on DB Fiddle:

EmployeeId | FunctionId | CompanyId | SortOrder | Primacy  
---------: | ---------: | --------: | :-------- | :--------
        54 |        273 |         8 | 1         | Primacy  
        54 |        273 |        19 | 2         | Primacy  
        54 |        273 |        32 | 3         | Primacy  
        54 |        273 |       151 | 4         | Primacy  
        54 |        897 |        49 | 5         | Secondary
        54 |        897 |        54 | 6         | Secondary
        54 |        897 |        77 | 7         | Secondary
        54 |        333 |        56 | 8         | Third    
        54 |        333 |        90 | 9         | Third    
        54 |        123 |        78 | 10        | Fourth