To find maximum salary using in-build oracle functions

1)     To find the third maximum salary of an employee

Method 1 – Using rownum keyword

select * from employee order by salary desc

SALARY

4531

3231

1231

431

300

200

100

31

select * from (select * from employee order by salary desc) salary2 where rownum<=3

SALARY

4531

3231

1231

select salary2.*, rownum rnum from (select * from employee ORDER BY salary DESC) salary2 where rownum <= 3

SALARY         RNUM

4531    1

3231    2

1231    3

select * from

(select salary2.*, rownum rnum from (select * from employee ORDER BY salary DESC) salary2 where rownum <= 3) where rnum=3

SALARY, RNUM

1231, 3

Below table structure with Data :

select * from employee

SALARY

100
100
200
300
400
500
1000
500

A) Using row_number in oracle

select e.*, row_number() over (order by salary desc) as row_num from Employee e 

SALARY  ROW_NUM

1000 1
500         2
500         3
400         4
300         5
200         6
100         7

100         8

select * from ( select e.*, row_number() over (order by salary desc) as row_num from Employee e ) where row_num = 2;

SALARY   ROW_NUM
500             2

B) Using rank() in oracle

select e.*, rank() over (order by salary desc) as rank from Employee e 

SALARY RANK

1000 1
500        2
500         2
400         4
300         5
200          6
100         7
100        7

select * from (select e.*, rank() over (order by salary desc) as rank from Employee e ) where rank = 2;

SALARY RANK

500                  2

500                  2

select e.*,dense_rank() over (order by salary desc) as dense_rank from Employee e

SALARY DENSE_RANK

1000         1

500                 2 

500                 2

400                 3

300                 4

200                 5

100                 6

100                 6

select * from 
(select e.*,dense_rank() over (order by salary desc) as dense_rank from Employee e) where dense_rank=2

SALARY DENSE_RANK

500                  2

500                  2

       2)      To Find all tables with a particular column_name

select table_name from dba_tab_columns where column_name='MSISDN'

select table_name,column_name from dba_tab_columns WHERE TABLE_NAME='MTX_PARTY'