Oracle sql KEEP clause select deptno, min(sal) , min(empno) keep (dense_rank first order by sal ) empnomin, max(sal) , max(empno) keep (dense_rank last order by sal ) empnomax from emp group by deptno; DEPTNO MIN(SAL) EMPNOMIN MAX(SAL) EMPNOMAX ---------- ---------- ---------- ---------- ---------- 10 1300 7934 5000 7839 20 800 7369 3000 7902 30 950 7900 2850 7698 ==================== 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 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 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. CREATE TABLE dense_rank_demo ( col VARCHAR2(10) NOT NULL ); INSERT ALL INTO dense_rank_demo(col) VALUES('A') INTO dense_rank_demo(col) VALUES('A') INTO dense_rank_demo(col) VALUES('B') INTO dense_rank_demo(col) VALUES('C') INTO dense_rank_demo(col) VALUES('C') INTO dense_rank_demo(col) VALUES('C') INTO dense_rank_demo(col) VALUES('D') SELECT 1 FROM dual; commit ; SQL> select * from dense_rank_demo; COL ---------- A A B C C C D SELECT col, DENSE_RANK () OVER ( ORDER BY col ) rank FROM dense_rank_demo; COL RANK ---------- ---------- A 1 A 1 B 2 C 3 C 3 C 3 D 4 7 rows selected.
Search This Blog
Total Pageviews
Sunday, 20 October 2024
Oracle sql KEEP clause
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
No comments:
Post a Comment