How to Find Nth Highest Salary?

This is very important for Oracle SQL interview.so Lets see how to Find Nth Highest Salary

Step 1:Create a simple N_TH_HIGHESTSALTable.

CREATE TABLE HIGHESTSAL
   ( EMP_ID NUMBER(2,0),
SALARY NUMBER(3,0)
   ) ;
step 2:Insert Values into the table N_TH_HIGHESTSAL
insert into n_th_highestSal values(1,200);
insert into n_th_highestSal values(2,700);
insert into n_th_highestSal values(3,300);
insert into n_th_highestSal values(4,600);
insert into n_th_highestSal values(5,800);
insert into n_th_highestSal values(6,500);
insert into n_th_highestSal values(7,900);
insert into n_th_highestSal values(8,400);

Step 3:Now use the RANK() function and retrieve the nth highest salary as per your requirement.
Eg:We will find 3 rd highest salary

select newta.* from (select emp_id,salary,rank() over (order by salary desc) sal_rank from N_TH_HIGHESTSAL)newta WHERE newta.sal_rank=3;

N:B:Try by yourself.

Q.2 How to Find 2nd Highest Salary?

Ans:
select max(SALARY) from n_th_highestSal where SALARY not in (select max(SALARY) from n_th_highestSal)
select max(SALARY) from n_th_highestSal where SALARY <(select max(SALARY) from n_th_highestSal)