Nth Highest queries. Or Find Nth Row.

Many a times we are asked to find the nth highest row depending on some criteria.

Here I am trying to compile all the shrewd and crude ways to find the 3rd Highest salary from our employees table.

hr@XE> select * from (select * from employees order by salary desc)
  2  where rownum < 4
  3  Minus
  4  Select * from (select * from employees order by salary desc)
  5  where rownum<3;

Notice that this may not produce the exact 3rd highest salary we should use distinct in that case.

select * from employees e
 where 3=(select count(distinct salary) from employees e1 -- Now this should return the 3rd highest.
          where e.salary<=e1.salary);

Using 10G analytics ---
select * from
(select e.* ,dense_rank() over (order by salary desc) rank_Sal
 from employees e)
where rank_sal=3 -- If you don't want to same ranks for two consecutive same salary values.              -------------------------ROW_NUMBER can be used.



The simplest (no same ranks for the same salary)

select * from (select rownum rank,e.* from employees e order by salary desc)
where rank=3;