How to get 5th,6th,7th highest salary in oracle sql?

Question

select * from ( select first_name,
                salary, 
                dense_rank() over (order by salary desc)r from emp)
where r = 3;

I tried above query with this I can get nth salary, but I want multiple 5th 6th and 7th highest salary in one query

Answer 1

I want multiple 5th 6th and 7th highest salary in one query

Just changing where condition to where r in (5,6,7) shall work for you.

SELECT first_name, salary
FROM (
  SELECT first_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM emp
)
WHERE rn IN (5, 6, 7);

Update :

Suppose if 5th and 6th number salary is same then in that case what would be the approach?

SELECT first_name, salary
FROM (
  SELECT first_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM emp
)
WHERE rnk IN (5, 6, 7);