I need to find that who earn more than the average salary of their own companies but if some company have more than one employees ( earn more than of their own company average salary) so then I need to select the highest one through of them
select w.*
from (select w.*, avg(salary) over (partition by company_name) as avgsalary
from works w
) w
where w.salary >w.avgsalary;
Result:-
----------------------------------------------------- Emp_Name| company_name |salary |avgsalary --------|-----------------------|--------|----------- emp 11 D Bank Corporation 39000 36800 emp 8 First Bank Corporation 40000 36280 emp 15 Nation Bank Corporation 42000 36800 emp 9 Small Bank Corporation 38900 37620 emp 5 Small Bank Corporation 39200 37620 emp 2 Small Bank Corporation 38000 37620
but I need the result as:
----------------------------------------------------- Emp_Name| company_name |salary |avgsalary --------|-----------------------|--------|----------- emp 11 D Bank Corporation 39000 36800 emp 8 First Bank Corporation 40000 36280 emp 15 Nation Bank Corporation 42000 36800 emp 5 Small Bank Corporation 39200 37620
the salary of emp 5 greater than emp 9 and 2. so that’s why first result unaccept. please help me to find second result by my query
Advertisement
Answer
I would add ROW_NUMBER to the mix here:
select w.*
from (
select w.*, avg(salary) over (partition by company_name) as avgsalary,
row_number() over (partition by company_name order by salary desc) rn
from works w
) w
where
salary > avgsalary and
rn = 1;
The logical difference in the above query versus your original one is that now should there be two or more employees whose salary exceed the average, only the one with the highest salary would be returned. For the additional edge case of two salaries ties with higher than average, you should specify additional logic on how to break that tie (or, you could replace ROW_NUMBER above with RANK, and return both of them).