Write SQL query to get the nth highest salary among all Employees.

Given a Employee Table with two columns
ID, Salary
10, 2000
11, 5000
12, 3000

Answer:

  • Option 1: Use SubqueryWe can use following sub query approach for this:

    SELECT *
    FROM Employee emp1
    WHERE (N-1) = (
    SELECT COUNT(DISTINCT(emp2.salary))
    FROM Employee emp2
    WHERE emp2.salary > emp1.salary)

  • Option 2: Using Rownum in OracleSELECT * FROM (
    SELECT emp.*,
    row_number() OVER (ORDER BY salary DESC) rnum
    FROM Employee emp
    )
    WHERE rnum = n;
Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*