There many ways to find nth highest salary.
Consider below EMP table structure
| EMPNNO | EMPNAME | SALARY | DEPTNO |
| 10 | Bill | 12000 | 5 |
| 11 | Solomon | 10000 | 5 |
| 12 | Susan | 10000 | 5 |
| 13 | Wendy | 9000 | 1 |
| 14 | Benjamin | 7500 | 1 |
| 15 | Tom | 7600 | 1 |
| 16 | Henry | 8500 | 2 |
| 17 | Robert | 9500 | 2 |
| 18 | Paul | 7700 | 2 |
| 19 | Dora | 8500 | 3 |
| 20 | Samuel | 6900 | 3 |
| 21 | Mary | 7500 | 3 |
| 22 | Daniel | 6500 | 4 |
| 23 | Ricardo | 7800 | 4 |
| 24 | Mark | 7200 | 4 |
List of all employees,salary,rank in descending order
| SALARY | EMPNO | RANK |
| 12000 | 10 | 1 |
| 10000 | 11 | 2 |
| 10000 | 12 | 2 |
| 9000 | 13 | 3 |
| 7500 | 14 | 4 |
| 7600 | 15 | 5 |
| 8500 | 16 | 5 |
| 9500 | 17 | 6 |
| 7700 | 18 | 7 |
| 8500 | 19 | 8 |
| 6900 | 20 | 9 |
| 7500 | 21 | 9 |
| 6500 | 22 | 10 |
| 7800 | 23 | 11 |
| 7200 | 24 | 12 |
1) Find highest salary/salaries using DENSE_RANK() function
select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 1;
EMPNO SALARY RK
———- ———- ———-
10 12000 1
2) Find 2nd highest salary/salaries using DENSE_RANK() function
select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 2;
EMPNO SALARY RK
———- ———- ———-
11 10000 2
12 10000 2
If you replace 2 with N in above query it will provide Nth highest salary/salaries.
3. Find 2nd highest salary using ROW_NUMBER() function
select * from (select empno, salary, row_number() over(order by salary desc) rk from emp order by salary desc ) where rk = 2;
EMPNO SALARY RK
———- ———- ———-
11 10000 2
Note that this query will not give multiple records if there are employees with same salaries ( compare query (2) and (3) )
4. Find 2nd highest salary using ROWNUM
select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2;
EMPNO SALARY RK
———- ———- ———-
11 10000 2
Note that this query also will not give multiple records if there are employees with same salaries ( compare query (2) and (4) )
Conclusion :- Use DENSE_RANK, ROW_NUMBER(), ROWNUM with respect to the requirement. As per my experience query with ROW_NUMBER() is most appropriate and good for performance.
You may interested in below posts also
RANK() function
DENSE_RANK() function
Autonomous Transaction
Cursors
Soundex function
Oracle Interview Questions
Awesome…..
Gud one.Keep it up.its very useful…
Thanks
select salary from employee e where (n-1)=(select count(distinct(salary)) from employee e1 where e1.salary>e.salary)
select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2; This will NOT give you second highest salary. It will give you second lowest salary.
For second highest salary, use below SQL:
select * from (select name, salary, rownum rank from employee order by salary desc) where rank = 2;
Both the query mentioned in your comment are same (both use order by salary desc).
Sorry for typo..
here is the right query:
select * from (select name, salary, rownum rank from emp order by salary desc) where rank ((select count(1) from emp)-1 );
Guys!!
“select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2;”
this will neither give you the second highest salary nor the second lowest.
It will fetch the second latest record inserted.
I mean if you use rk =1 in the where clause then it will fetch you the latest record inserted.
I guess use of ROW_NUMBER() function is really a good idea but if at all ‘rownum’ is your favorite then you can use
SELECT *
FROM (select salary.* ,rownum rk from
(select * from emp ORDER BY salary DESC) salary
where rownum <= 2 )
WHERE rk = 2;