Select nth highest salary

This is very popular interview question asked for beginners to experienced programmers.

I will explain how we can find the nth highest salary using Oracle row_number() analytic function.

Let me first explain what is row_number() analytic function.

row_number() assign a unique number to each row to which it applied based on the order by clause(either descending order or ascending order).

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query.

Query:

SELECT *
FROM
  ( SELECT employee.*,
           row_number() over (
                              ORDER BY employee.salary DESC) rownumber
   FROM employee employee)
WHERE rownumber=n;

As you see the above query, first we are using the inner query which assign a number for each row starting from 1. Since we are doing order by desc for the salary, the salaries will be order by highest to smallest.

Since the rows are arranged in descending order, the row with the highest salary will have a 1 for the row number.Since we know the row numbers we just pass the row number(n) and it will compare and return the result.

That's all about selecting the nth highest salary.

Thanks for visiting my blog!!!!!