Oracle SQL - Print nth maximum salary using DENSE_RANK function

In this post, I explained you how to get nth Maximum/Max salary.

Example Table:

--------------------

CREATE TABLE BLOG_EMP_SALARY_DTLS

(BESD_EMP_ID         VARCHAR2(15),

 BESD_EMP_NAME       VARCHAR2(150),

 BESD_GENDER         VARCHAR2(1)         DEFAULT 'M' NOT NULL,

 BESD_DOJ            DATE,

 BESD_DOB            DATE,

 BESD_DEPT           VARCHAR2(100),

 BESD_SALARY         NUMBER(12, 2)       DEFAULT 0 NOT NULL);

INSERT SCRIPT

-------------

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100001', 'JOSEPH', 'M', '01-JAN-2010', '25-JUL-1988', 'ADMIN', 15000) 

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100002', 'KARIM', 'M', '15-MAR-2012', '01-AUG-1987', 'HRM', 18500)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100003', 'SURESH', 'M', '16-MAR-2012', '01-MAR-1990', 'PRODUCTION', 11250) 

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100004', 'RANJITH', 'M', '1-APR-2012', '01-AUG-1990', 'PRODUCTION', 11250)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100005', 'SATHISH', 'M', '05-SEP-2015', '01-MAR-1995', 'ADMIN', 75000)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100006', 'GANESH', 'M', '05-AUG-2015', '01-SEP-1992', 'ADMIN', 16000)

SAMPLE OUTPUT

-------------

SELECT * FROM BLOG_EMP_SALARY_DTLS

Nth Salary Query

----------------

SELECT *

  FROM (SELECT besd_emp_id,

               besd_emp_name,

               besd_salary,

               DENSE_RANK () OVER (ORDER BY besd_salary DESC) bsed_sal_rank

          FROM blog_emp_salary_dtls)

 WHERE bsed_sal_rank = 1;

OUTPUT: 1 (First Salary)

------------------------

OUTPUT: 2 (Second  Salary)

------------------------

OUTPUT: 3 (Third Salary)

------------------------

OUTPUT: 4 (Fourth Salary)

-------------------------

Both Suresh and Ranjith have same salary, query return 4th salary both of them.

Thanks & Regards

R. Kalaiyarasan

+91 9944809554