Finding Nth highest salary in a table

Finding Nth highest salary in a table, Here is a way to do this task using dense_rank() function.


select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;


DENSE_RANK :

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

2. This function accepts arguments as any numeric data type and returns NUMBER.

3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
 
4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.