TechNet Wiki v2

Different Methods to get nth maximum salary in a given table - TechNet Articles - United States (English) - TechNet Wiki

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

(7566,

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.