Top N query - 3rd highest salary

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
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20          3
2. 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          3
But 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

7 comments:

  1. hey thanks .. this helped me to do my practicals/....

    ReplyDelete
  2. yor r welcome, keep following this blog for more tips.

    ReplyDelete
  3. thanks for providing this information !!!

    ReplyDelete
  4. It 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

    ReplyDelete
  5. simply use <= in place of < as

    select * from (select emp.*, dense_rank() over (order by sal desc) rn from scott.emp) where rn <= 3

    ReplyDelete
  6. Q1. SELECT TOP 1 salary
    FROM (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

    ReplyDelete
    Replies
    1. My blog is not a forum, and your questions seem to be educational assignment. Do it your self or google it.

      Delete