count(*) over partition, order by, range unbounded preceding

 
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>
SQL> set echo on
SQL>
SQL> break on deptno skip 1
SQL>
SQL> update emp set sal = 99 where deptno = 30;

6 rows updated.

SQL>
SQL> select *
  2    from (select deptno, ename, sal,
  3                 row_number() over ( partition by deptno
  4                                     order by sal desc ) rn
  5            from emp )
  6   where rn <= 3
  7  /

DEPTNO ENAME           SAL       RN
------ ---------- -------- --------
    10 KING        5000.00     1.00
       CLARK       2450.00     2.00
       MILLER      1300.00     3.00

    20 SCOTT       3000.00     1.00
       FORD        3000.00     2.00
       JONES       2975.00     3.00

    30 ALLEN         99.00     1.00
       BLAKE         99.00     2.00
       MARTIN        99.00     3.00

9 rows selected.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

Related examples in the same category

1.	count(*) over partition by, order by, range unbounded preceding
2.	Employee salary report with avg salary for the previous 12 months
3.	avg over range between
4.	Is our average total_order_price increasing or decreasing?
5.	analytic order-by clause
6.	avg over and avg over order by
7.	Sum over order by
8.	Sum over partition by and order by
9.	avg over order by range
10.	average 5 before, after
11.	Row-ordering is done first and then the moving average
12.	Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
13.	SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
14.	Use the COUNT aggregate analytical function to show how many rows are included in each window
15.	To see how the moving average window can expand
16.	Uses dates and logical offset of seven days preceding
17.	A seven-day MAX and MIN on Tuesdays
18.	A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
19.	Displaying a Running Total Using SUM as an Analytical Function
20.	Reporting on a Sum

count(*) over partition, order by, range unbounded preceding : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

Related examples in the same category