Decode the result from row_number over, partition by, order by : ROW_NUMBER « Analytical Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Analytical Functions
3. ROW_NUMBER

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.

   
  

Related examples in the same category