There are many methods to get N-th Maximum salary in a table.Here are few methods.


CREATE TABLE EMP
    (
       EMPNO INT NOT NULL,
        ENAME VARCHAR(10),
        JOB VARCHAR(9),
        MGR INT,
        HIREDATE DATE,
        SAL INT,
        COMM INT,
        DEPTNO INT
    )
     
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        '17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '20-FEB-1981', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '22-FEB-1981', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '2-APR-1981',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '28-SEP-1981', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '1-MAY-1981',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '9-JUN-1981',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '09-DEC-1982', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '17-NOV-1981', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '8-SEP-1981',  1500, NULL, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '12-JAN-1983', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '3-DEC-1981',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '3-DEC-1981',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '23-JAN-1982', 1300, NULL, 10);
 
SELECT * FROM EMP ORDER BY SAL DESC
 
 
 
--DIFFERENT METHODES TO GET N-TH MAXIMUM SALARY IN A TABLE.
-- METHODE-1
SELECT A.EMPNO,A.ENAME,A.SAL
 FROM EMP A JOIN EMP B ON A.SAL<=B.SAL
 GROUP BY A.SAL,A.EMPNO,A.ENAME
 HAVING COUNT(A.SAL)=4
 
 -- METHODE-2
  SELECT TOP 1 *
 FROM
 (
 SELECT TOP 4 * FROM EMP ORDER BY SAL DESC
 ) TEMP
 ORDER BY SAL ASC
 
 -- METHODE-3
 WITH CTE
 AS
 (
 SELECT *,ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RNUM FROM EMP
 )
 SELECT * FROM CTE WHERE RNUM=4
 
 -- METHODE- 4
 SELECT * FROM EMP WHERE SAL=
 (
 SELECT MIN(SAL) AS SAL
 FROM
 (
 SELECT TOP 4 EMPNO,ENAME,HIREDATE,SAL FROM EMP ORDER BY SAL DESC
 ) TEMP
 )
 
 -- METHODE- 5
 SELECT * FROM EMP A
 WHERE
 (SELECT COUNT(*) FROM EMP WHERE SAL>=A.SAL)=4


Thanks.