I have a query to compute the 3rd highest salary for an employee as shown below.
SELECT MAX(salary) FROM employee WHERE emp_no = 1 AND salary < (SELECT MAX(salary) FROM employee WHERE emp_no = 1 AND salary NOT IN (SELECT MAX(salary) FROM employee WHERE emp_no = 1))
How can I apply this query to give the 3rd highest salary for each employee which can be fetched by the query
select distinct(emp_no) from employee
Note: without using special functions like
sample table -------------------- EMP SALARY -------------------- 1 1000 1 1000 1 900 1 800--->Required 2 1000 2 1000 2 500 2 400---->Required
You are looking for each employee’s third highest salary. It can happen that we find the same salary for an employee multiple times in the table, as your sample data shows. So, make the salaries per employee distinct, then rank the rows with
DENSE_RANK (which doesn’t matter with distinct values) and then pick the third ranked.
select emp_no, salary from ( select distinct emp_no, salary, dense_rank() over (partition by emp_no order by salary desc) as rnk from employee ) ranked where rnk = 3 order by emp_no, salary;
An alternative would be to count higher salaries in a subquery and select those salaries where exist two higher salaries for the employee:
with distinct_salaries as ( select distinct emp_no, salary from employee ) select * from distinct_salaries where ( select count(*) from distinct_salaries higher where higher.emp_no = distinct_salaries.emp_no and higher.salary > distinct_salaries.salary ) = 2;