Find nth Max salary in a table (Oracle SQL)

Note : DB Dump for all the below queries is at the bottom

Find max salary from a table

select max(AVAIL_BALANCE) from ACCOUNT;

Find 2nd Max salary from a table

select max(AVAIL_BALANCE) from ACCOUNT 
where AVAIL_BALANCE not in (select max(AVAIL_BALANCE) from ACCOUNT);--2nd max balance

Find the rank of all the records using rownum

select AVAIL_BALANCE,rownum as rank from 
(select AVAIL_BALANCE from ACCOUNT order by AVAIL_BALANCE desc);--all rank using row num

Find a particular rank of a record using rownum

select * from (
select AVAIL_BALANCE,rownum as rank from (
select AVAIL_BALANCE from ACCOUNT order by AVAIL_BALANCE desc
)
) where rank=3;--the inner table is called as inline view

Find rank based on salary and also salary can be repeated

select AVAIL_BALANCE,rank() over (order by AVAIL_BALANCE desc) as rank from ACCOUNT;--all balance using rank

Find the nth Max salary of a table

select * from (select AVAIL_BALANCE,rank() over (order by AVAIL_BALANCE desc) as rank from ACCOUNT) where rank=5;--all balance using rank

Find the rank of salary in a table if the records are repeated

select AVAIL_BALANCE,dense_rank() over (order by AVAIL_BALANCE desc) from ACCOUNT; --all balance using dense rank

Find the nth Max salary of a table if data is repeated

select * from (select AVAIL_BALANCE,dense_rank() over (order by AVAIL_BALANCE desc) as rank from ACCOUNT) where rank=6; 

Find the rank of salary in a table based on department

select * from (select AVAIL_BALANCE,OPEN_BRANCH_ID,dense_rank() over (partition by OPEN_BRANCH_ID order by AVAIL_BALANCE desc) as rank from ACCOUNT);

Find the nth Max salary in a table based on department

select * from (select AVAIL_BALANCE,OPEN_BRANCH_ID,dense_rank() over (partition by OPEN_BRANCH_ID order by AVAIL_BALANCE desc) as rank from ACCOUNT) where OPEN_BRANCH_ID=2 and rank=3;

DB Dump :

create table ACCOUNT (
        ACCOUNT_ID number(10,0) not null,
        AVAIL_BALANCE float,
        CLOSE_DATE date,
        LAST_ACTIVITY_DATE date,
        OPEN_DATE date not null,
        PENDING_BALANCE float,
        STATUS varchar2(10 char),
        CUST_ID number(10,0),
        OPEN_BRANCH_ID number(10,0) not null,
        OPEN_EMP_ID number(10,0) not null,
        PRODUCT_CD varchar2(10 char) not null
    );


select * from accounts








Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (1,1057.75,null,to_date('04-01-05','DD-MM-RR'),to_date('15-01-00','DD-MM-RR'),1057.75,'ACTIVE',1,2,10,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (2,500,null,to_date('19-12-04','DD-MM-RR'),to_date('15-01-00','DD-MM-RR'),500,'ACTIVE',1,2,10,'SAV');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (3,3000,null,to_date('30-06-04','DD-MM-RR'),to_date('30-06-04','DD-MM-RR'),3000,'ACTIVE',1,2,10,'CD');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (4,2258.02,null,to_date('27-12-04','DD-MM-RR'),to_date('12-03-01','DD-MM-RR'),2258.02,'ACTIVE',2,2,10,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (5,200,null,to_date('11-12-04','DD-MM-RR'),to_date('12-03-01','DD-MM-RR'),200,'ACTIVE',2,2,10,'SAV');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (6,1057.75,null,to_date('30-11-04','DD-MM-RR'),to_date('23-11-02','DD-MM-RR'),1057.75,'ACTIVE',3,3,13,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (7,2212.5,null,to_date('05-12-04','DD-MM-RR'),to_date('15-12-02','DD-MM-RR'),2212.5,'ACTIVE',3,3,13,'MM');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (8,534.12,null,to_date('03-01-05','DD-MM-RR'),to_date('12-09-03','DD-MM-RR'),534.12,'ACTIVE',4,1,1,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (9,767.77,null,to_date('24-10-04','DD-MM-RR'),to_date('15-01-00','DD-MM-RR'),767.77,'ACTIVE',4,1,1,'SAV');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (10,5487.09,null,to_date('11-11-04','DD-MM-RR'),to_date('30-09-04','DD-MM-RR'),5487.09,'ACTIVE',4,1,1,'MM');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (11,2237.97,null,to_date('05-01-05','DD-MM-RR'),to_date('27-01-04','DD-MM-RR'),2897.97,'ACTIVE',5,4,16,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (12,122.37,null,to_date('29-11-04','DD-MM-RR'),to_date('24-08-02','DD-MM-RR'),122.37,'ACTIVE',6,1,1,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (13,10000,null,to_date('28-12-04','DD-MM-RR'),to_date('28-12-04','DD-MM-RR'),10000,'ACTIVE',6,1,1,'CD');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (14,5000,null,to_date('12-01-04','DD-MM-RR'),to_date('12-01-04','DD-MM-RR'),5000,'ACTIVE',7,2,10,'CD');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (15,3487.19,null,to_date('03-01-05','DD-MM-RR'),to_date('23-05-01','DD-MM-RR'),3487.19,'ACTIVE',8,4,16,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (16,387.99,null,to_date('12-10-04','DD-MM-RR'),to_date('23-05-01','DD-MM-RR'),387.99,'ACTIVE',8,4,16,'SAV');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (17,125.67,null,to_date('15-12-04','DD-MM-RR'),to_date('30-07-03','DD-MM-RR'),125.67,'ACTIVE',9,1,1,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (18,9345.55,null,to_date('28-10-04','DD-MM-RR'),to_date('28-10-04','DD-MM-RR'),9845.55,'ACTIVE',9,1,1,'MM');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (19,1500,null,to_date('30-06-04','DD-MM-RR'),to_date('30-06-04','DD-MM-RR'),1500,'ACTIVE',9,1,1,'CD');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (20,23575.12,null,to_date('15-12-04','DD-MM-RR'),to_date('30-09-02','DD-MM-RR'),23575.12,'ACTIVE',10,4,16,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (21,0,null,to_date('28-08-04','DD-MM-RR'),to_date('01-10-02','DD-MM-RR'),0,'ACTIVE',10,4,16,'BUS');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (22,9345.55,null,to_date('14-11-04','DD-MM-RR'),to_date('22-03-04','DD-MM-RR'),9345.55,'ACTIVE',11,2,10,'BUS');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (23,38552.05,null,to_date('15-12-04','DD-MM-RR'),to_date('30-07-03','DD-MM-RR'),38552.05,'ACTIVE',12,4,16,'CHK');
Insert into ACCOUNT (ACCOUNT_ID,AVAIL_BALANCE,CLOSE_DATE,LAST_ACTIVITY_DATE,OPEN_DATE,PENDING_BALANCE,STATUS,CUST_ID,OPEN_BRANCH_ID,OPEN_EMP_ID,PRODUCT_CD) values (24,50000,null,to_date('17-12-04','DD-MM-RR'),to_date('22-02-04','DD-MM-RR'),50000,'ACTIVE',13,3,13,'SBL');

Leave a Comment