Lets suppose we want to find a employee who is getting 3rd highest salary
We can try it with rownum but it may output worng result if first 2 employees have same salary
1. select * from (select emp.*, rownum rn from (select * from scott.emp order by sal desc) emp) where rn = 3
3. select * from (select emp.*, dense_rank() over (order by sal desc) rn from scott.emp) where rn = 3
4. select * from scott.emp b where (select count(distinct sal) from scott.emp a where a.sal >= b.sal) = 3 order by deptno, sal
Related Links:
- Top N query - 3rd highest salary
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause
We can try it with rownum but it may output worng result if first 2 employees have same salary
1. select * from (select emp.*, rownum rn from (select * from scott.emp order by sal desc) emp) where rn = 3
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 32. select * from (select emp.*, row_number() over (order by sal desc) rn from scott.emp) where rn = 3
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 3But the better solution is dense_rank
3. select * from (select emp.*, dense_rank() over (order by sal desc) rn from scott.emp) where rn = 3
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 3
4. select * from scott.emp b where (select count(distinct sal) from scott.emp a where a.sal >= b.sal) = 3 order by deptno, sal
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20
Related Links:
- Top N query - 3rd highest salary
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause
hey thanks .. this helped me to do my practicals/....
ReplyDeleteyor r welcome, keep following this blog for more tips.
ReplyDeletethanks for providing this information !!!
ReplyDeleteIt is a great example to find a specific record. But let's say I am interested in first top 2 salaries or top 3 salaries in descending order. how do I achieve that? thanks, Atila
ReplyDeletesimply use <= in place of < as
ReplyDeleteselect * from (select emp.*, dense_rank() over (order by sal desc) rn from scott.emp) where rn <= 3
Q1. SELECT TOP 1 salary
ReplyDeleteFROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a
ORDER BY salary
what does this ' a ' means here.
Q2. How to auto number the rows in a table in Oracle SQL*9 ?
Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?
Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?
Hint : you have a employee(empid,empname,salary) table
Please HELP ASAP!!!
jainvijesh90@gmail.com
My blog is not a forum, and your questions seem to be educational assignment. Do it your self or google it.
Delete