Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

Decode the result from row_number over, partition by, order by

SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,   2                    ENAME VARCHAR2(10),   3                    JOB VARCHAR2(9),   4                    MGR NUMBER(4),   5                    HIREDATE DATE,   6                    SAL NUMBER(7, 2),   7                    COMM NUMBER(7, 2),   8                    DEPTNO NUMBER(2)); Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;    EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO -------- ---------- --------- -------- --------- -------- -------- ------  7369.00 SMITH      CLERK      7902.00 17-DEC-80   800.00              20  7499.00 ALLEN      SALESMAN   7698.00 20-FEB-81  1600.00   300.00     30  7521.00 WARD       SALESMAN   7698.00 22-FEB-81  1250.00   500.00  7566.00 JONES      MANAGER    7839.00 02-APR-81  2975.00              20  7654.00 MARTIN     SALESMAN   7698.00 28-SEP-81  1250.00  1400.00     30  7698.00 BLAKE      MANAGER    7839.00 01-MAY-81  2850.00  7782.00 CLARK      MANAGER    7839.00 09-JUN-81  2450.00              10    EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO -------- ---------- --------- -------- --------- -------- -------- ------  7788.00 SCOTT      ANALYST    7566.00 09-DEC-82  3000.00              20  7839.00 KING       PRESIDENT          17-NOV-81  5000.00              10  7844.00 TURNER     SALESMAN   7698.00 08-SEP-81  1500.00      .00     30  7876.00 ADAMS      CLERK      7788.00 12-JAN-83  1100.00              20  7900.00 JAMES      CLERK      7698.00 03-DEC-81   950.00              30    EMPNO ENAME      JOB            MGR HIREDATE       SAL     COMM DEPTNO -------- ---------- --------- -------- --------- -------- -------- ------  7902.00 FORD       ANALYST    7566.00 03-DEC-81  3000.00              20  7934.00 MILLER     CLERK      7782.00 23-JAN-82  1300.00              10 14 rows selected. SQL> SQL> set echo on SQL> SQL> select max(count(*)) from emp group by deptno, job; MAX(COUNT(*)) -------------          4.00 SQL> SQL> column deptno format 999 SQL> column sal_1 format 9999 SQL> column sal_2 format 9999 SQL> column sal_3 format 9999 SQL> column sal_4 format 9999 SQL> column ename_1 format a6 SQL> column ename_4 format a6 SQL> SQL> select deptno, job,   2         max( decode( rn, 1, ename, null )) ename_1,   3         max( decode( rn, 1, sal, null )) sal_1,   4         max( decode( rn, 2, ename, null )) ename_2,   5         max( decode( rn, 2, sal, null )) sal_2,   6         max( decode( rn, 3, ename, null )) ename_3,   7         max( decode( rn, 3, sal, null )) sal_3,   8         max( decode( rn, 4, ename, null )) ename_4,   9         max( decode( rn, 4, sal, null )) sal_4  10    from (  select deptno, job, ename, sal,  11                   row_number() over ( partition by deptno, job  12                                           order by sal, ename ) rn  13              from emp  14             )  15  group by deptno, job  16  / DEPTNO JOB       ENAME_ SAL_1 ENAME_2    SAL_2 ENAME_3    SAL_3 ENAME_ SAL_4 ------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----     10 CLERK     MILLER  1300        MANAGER   CLARK   2450        PRESIDENT KING    5000     20 ANALYST   FORD    3000 SCOTT       3000        CLERK     SMITH    800 ADAMS       1100        MANAGER   JONES   2975     30 CLERK     JAMES    950        MANAGER   BLAKE   2850        SALESMAN  MARTIN  1250 WARD        1250 TURNER      1500 ALLEN   1600 9 rows selected. SQL> drop table emp; Table dropped.