Employe(emp_no,emp_name,salary) table has following data
7698 blak 16525
7566 jone 12566
7901 priy 12128
1234 luck 12128
7788 scott 3000
7499 allen 1600
7844 turner1500
7654 martin 1500
7521 ward 1250
7876 adams 1200
7900 james 1050
7369 jhon 800
How to find the 3rd larget salary
SELECT salary
FROM (SELECT salary, ROW_NUMBER () OVER (ORDER BY salary DESC) rn
FROM Employee e)
WHERE rn = 3
or
SELECT LEVEL, MAX(salary) salary
FROM employee
WHERE LEVEL = 3
CONNECT BY PRIOR salary > salary
GROUP BY LEVEL;
it will return 3rd larget salary but if the table has duplicate values in the salary field it will fail
to over come this we have to use
SELECT *
FROM (SELECT emp_no,
salary,
DENSE_RANK () OVER (ORDER BY salary DESC) ranking
FROM employee)
WHERE ranking = 3
7901 12128 3
1234 12128 3
one more option
SELECT MAX (SAlary)
FROM (SELECT *
FROM ( SELECT *
FROM EMPloyee
ORDER BY SAlary DESC)
WHERE ROWNUM <= 3)
