Skip to content
Advertisement

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

Result:-

but I need the result as:

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:

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement