I try to find average salaries by following query

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;

​x
 
select w.*from (select w.*, avg(salary) over (partition by company_name) as avgsalary      from works w      ) wwhere 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

 
-----------------------------------------------------Emp_Name| company_name          |salary  |avgsalary   --------|-----------------------|--------|-----------emp 11   D Bank Corporation  39000   36800emp 8    First Bank Corporation  40000   36280emp 15   Nation Bank Corporation 42000   36800emp 9    Small Bank Corporation  38900   37620emp 5    Small Bank Corporation  39200   37620emp 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

 
-----------------------------------------------------Emp_Name| company_name          |salary  |avgsalary   --------|-----------------------|--------|-----------emp 11   D Bank Corporation  39000   36800emp 8    First Bank Corporation  40000   36280emp 15   Nation Bank Corporation 42000   36800emp 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

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;

 
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) wwhere    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).

Advertisement

Answer