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
