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
Advertisement
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.
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