How to find the nth salary from employee table

This is a very common but tricky question for all the pl/sql interviews. Basically we have to find the nth highest salary from an employee salary table.

We have two common ways to get the nth salary or value of any column in a table.

1)      Using nested queries: - Follow the below steps to analyses this approach.

·         Create table esalary.

CREATE TABLE esalary (ename VARCHAR2(20), edep VARCHAR2(20), esal NUMBER(10));

·         Insert some dummy values in table esalary.

INSERT INTO esalary VALUES ('EMP1','Engineering',15000);

INSERT INTO esalary VALUES ('EMP2','Engineering',40000);

INSERT INTO esalary VALUES ('EMP3','Engineering',35000);

INSERT INTO esalary VALUES ('EMP4','Engineering',20000);

INSERT INTO esalary VALUES ('EMP5','Engineering',22000);

·         Find the nth number salary from salary table.

SELECT ESAL FROM (SELECT DISTINCT esal FROM (SELECT DISTINCT esal FROM esalary ORDER BY esal DESC) WHERE ROWNUM <= 3 ORDER BY esal ASC) WHERE ROWNUM = 1

Result: - 22000

Note: - Change ROWNUM<=3 in upper query with number for which you want to find the salary.

Example above query will return (22000) as the 3rd highest salary. 

Working Description: - The most inner sub query will return all the salaries from table in descending order then the outer sub query will fetch the first 3 (or the number we put in the query) highest salaries from the inner sub query result and sort them in ascending order and the outer most query will select the first value from the selected values.

2)      Using one nested query in WHERE clause: -

SELECT esal FROM esalary a WHERE 2 = (SELECT COUNT(*) FROM esalary b WHERE a.esal<=b.esal)

                Result: - 35000

       Working Description: - The upper query used only one sub query in the where clause. It also used the self-join to compare the one salary value of a table with all the other salary values of the table. Suppose we use 2 (means second highest salary) in above where clause so now the complete query will return the result only when the inner query in where clause will return 2. The inner where clause sub query  will compare all the salary of outer table alliance (given a name a) by a with all the salaries in the inner table alliance (given a name b) by b and return the number of values equal to or greater than the outer table alliance by a. In this case it will return 2 only when the second highest value of outer table is compared with all the inner table salaries.