Get the N-th highest value Or N-th lowest Value from a column in a table

I want to find the N-th highest or lowest salary from EMP table.

At first, create table emp:

create table emp

(empid int, salary number);


Insert some data into emp:


insert into emp values(1, 200);

insert into emp values(10, 150);

insert into emp values(15, 10000);

insert into emp values(6, 11100);

insert into emp values(4, 12000);

insert into emp values(5, 5000);

insert into emp values(20, 2000);

insert into emp values(30, 200);


insert into emp values(40, 200);

commit;

Select data from table: 

select * from emp;

Now i want to find out the third highest salary from the above employees:

select * from
emp a

where 3 = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);

Now i want to find out the second highest salary from the above employees:

select * from
emp a
where 2 = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);

Now i want to find out the second lowest salary from the above employees:

select * from
emp a
where 2 = (select count(distinct(b.salary)) from emp b where b.salary<=a.salary);

So for N-th highest or N-th lowest salary, i just include sql bind variable in the query:

select * from
emp a
where &N = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);



select * from
emp a
where &N = (select count(distinct(b.salary)) from emp b where b.salary<=a.salary);