Oracle "over" Analytic Function tips Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced Oracle SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Analytics

An analytic function is calculated over multiple rows and returns the result in the current row. The multiple row function could be an aggregate function, like COUNT, or a pure analytic function like RANK.

OVER

The scope of an analytic function is defined in an OVER clause. OVER is a mandatory keyword for all analytic functions. Giving () as the parameter to OVER indicates the widest possible scope - ?all?. In the example below, the total number of departments over the whole table is counted.

SELECT
   DEPTNO,
   COUNT(*) OVER () "NUMBER OF DEPARTMENTS"
FROM
   DEPT;

    DEPTNO COUNT(*)OVER()
---------- --------------
        10              4
        20              4
        30              4
        40              4

The total count of departments is returned next to each department. Note the COUNT function is used here without the GROUP BY clause.

It is possible to use the KEEP clause to retrieve additional columns.

SELECT
   ENAME,
   SAL,
   MIN(ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL) OVER (),
   MIN(SAL) OVER ()
FROM
   EMP;

ENAME             SAL MIN(ENAME) MIN(SAL)OVER()
---------- ---------- ---------- --------------
SMITH             800 SMITH                 800
ALLEN            1600 SMITH                 800
WARD             1250 SMITH                 800
JONES            2975 SMITH                 800
MARTIN           1250 SMITH                 800
BLAKE            2850 SMITH                 800
CLARK            2450 SMITH                 800
SCOTT            3000 SMITH                 800
KING             5000 SMITH                 800
TURNER           1500 SMITH                 800
ADAMS            1100 SMITH                 800
JAMES             950 SMITH                 800
FORD             3000 SMITH                 800
MILLER           1300 SMITH                 800

The name and salary of the employee with the lowest salary is returned.