Write an sql to give the nth highest in a table?

The nth highest in a table can be easily achieved in oracle with help of analytical function RANK.

Let me explain this:
Suppose we have a table Emp_Sal with the structure as
CREATE TABLE EMP_SAL
  (
    ID     NUMBER,
    NAME   VARCHAR2(200 BYTE),
    SALARY NUMBER(10,2),
    DEPT   VARCHAR2(10 BYTE)
  )

Now will insert some data in this table by executing below insert sql statements.

insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (1,'Salim',50000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (2,'kamlesh',5000,'Admin');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (3,'Alok',30000,'HR');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (4,'Suresh',70000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (5,'Vikash',40000,'HR');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (6,'Shyam',50000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (7,'Vijay',120000,'Admin');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (8,'Ravi',6000,'Admin');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (9,'Ganesh',90000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (10,'Rahul',25000,'HR');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (11,'Mahesh',150000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (12,'Jony',350000,'IT');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (13,'Vivek',8000,'Admin');
insert into Emp_Sal(ID, NAME, SALARY, DEPT) Values (14,'Raja',120000,'IT');
commit;


Now we have 14 records in Emp_Sal table and want to select 4th highest salary.
For this we can write SELECT statement like :

SELECT ID, NAME, salary FROM
(
    SELECT A.*, rank() OVER (ORDER BY salary) rank
    FROM Emp_Sal A
)
where rank = 4;
Result will be as ;
id     Name   Salary
10    Rahul    25000

Here rank is an analytical function, used to apply rank on a record in a group
of rows and if we are not using any partitioning the entire table will be a group.
This always return a number.

And if we require 2nd highest salary from Emp_Sal then we can write as :

SELECT ID, NAME, salary FROM
(
    SELECT A.*, rank() OVER (ORDER BY salary) rank
    FROM Emp_Sal A
)
where rank = 2;
The result will be:
id   Name   Salary
8    Ravi    6000

For getting any no of highest, we only need to change the number in outer where clause.
This is because we have applied an order on the salary column for hole Emp_Sal table by
using the rank analytical function. This rank function in the sub query is providing additional
column containing the rank of salary for entire table.
So below is a generalised statement for any highest.

SELECT ID, NAME, salary FROM
(
    SELECT A.*, rank() OVER (ORDER BY salary) rank
    FROM Emp_Sal A
)
where rank = n;

Now suppose we want 2nd highest salary records for every department.
The only difference between the current and earlier requirement is that earlier we were
applying the rank function over the table so the entire table was a group but now we need
to create groups of records based on department and then need to apply the separate rank
on each department. To make separate group of record based on the departments we can use
partition by clause, like:

SELECT ID, NAME, salary FROM
(
    SELECT A.*, rank() OVER (partition by dept ORDER BY salary) rank
    FROM Emp_Sal A
)
where RANK = 2;
Here partition by dept will first make the separate portion for each department and order by salary
will perform the ordering (by default asc) after that rank will be applied.

Out put is
id   Name   Salary 

8    Ravi    6000
3    Alok    30000
6    Shyam    50000

This is the most frequently asked question by interviewers.