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