SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,
2 RANK()OVER(ORDER BY SAL) AS RANK,
3 DENSE_RANK()OVER(ORDER BY SAL) AS DENSE_RANK
4 FROM EMP;
EMPNO ENAME DEPTNO SAL RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 1 1
7900 JAMES 30 950 2 2
7876 ADAMS 20 1100 3 3
7521 WARD 30 1250 4 4
7654 MARTIN 30 1250 4 4
7934 MILLER 10 1300 6 5
7844 TURNER 30 1500 7 6
7499 ALLEN 30 1600 8 7
7782 CLARK 10 2450 9 8
7698 BLAKE 30 2850 10 9
7566 JONES 20 2975 11 10
7788 SCOTT 20 3000 12 11
7902 FORD 20 3000 12 11
7839 KING 10 5000 14 12
14 rows selected.
SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,
2 RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS RANK,
3 DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANK
4 FROM EMP ORDER BY DEPTNO;
EMPNO ENAME DEPTNO SAL RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 1 1
7782 CLARK 10 2450 2 2
7839 KING 10 5000 3 3
7369 SMITH 20 800 1 1
7876 ADAMS 20 1100 2 2
7566 JONES 20 2975 3 3
7788 SCOTT 20 3000 4 4
7902 FORD 20 3000 4 4
7900 JAMES 30 950 1 1
7654 MARTIN 30 1250 2 2
7521 WARD 30 1250 2 2
7844 TURNER 30 1500 4 3
7499 ALLEN 30 1600 5 4
7698 BLAKE 30 2850 6 5
14 rows selected.
SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP;
EMPNO ENAME DEPTNO SAL DENSE_RANK
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7902 FORD 20 3000 2
7788 SCOTT 20 3000 2
7566 JONES 20 2975 3
7698 BLAKE 30 2850 4
7782 CLARK 10 2450 5
7499 ALLEN 30 1600 6
7844 TURNER 30 1500 7
7934 MILLER 10 1300 8
7521 WARD 30 1250 9
7654 MARTIN 30 1250 9
7876 ADAMS 20 1100 10
7900 JAMES 30 950 11
7369 SMITH 20 800 12
14 rows selected.
SQL> SELECT * FROM (
2 SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS RNK FROM EMP
3 ) WHERE RNK<=5;
EMPNO ENAME DEPTNO SAL RNK
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7788 SCOTT 20 3000 2
7902 FORD 20 3000 2
7566 JONES 20 2975 3
7698 BLAKE 30 2850 4
7782 CLARK 10 2450 5
6 rows selected.
SQL>
Queries:
SELECT * FROM EMP;
SELECT EMPNO,ENAME,DEPTNO,SAL,
RANK()OVER(ORDER BY SAL) AS RANK,
DENSE_RANK()OVER(ORDER BY SAL) AS DENSE_RANK
FROM EMP;
SELECT EMPNO,ENAME,DEPTNO,SAL,
RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS RANK,
DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANK
FROM EMP ORDER BY DEPTNO;
SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP;
SELECT * FROM (
SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS RNK FROM EMP
) WHERE RNK<=5;
No comments:
Post a Comment