Skip to content
Advertisement

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 :

Output :

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 :

Advertisement

Answer

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

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   
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement