Using OLAP specifications

Example: Ranking and row numbering

 SELECT EMPNO, SALARY, 
        RANK() OVER(ORDER BY SALARY DESC),
        DENSE_RANK() OVER(ORDER BY SALARY DESC),
        ROW_NUMBER() OVER(ORDER BY SALARY DESC)  
 FROM EMPLOYEE
 FETCH FIRST 10 ROWS ONLY

This query returns the following information.

In this example, the SALARY descending order with the top 10 returned. The RANK column shows the relative ranking of each salary. Notice that there are two rows with the same salary at position 2. Each of those rows is assigned the same rank value. The following row is assigned the value of 4. RANK returns a value for a row that is one more than the total number of rows that precede that row. There are gaps in the numbering sequence whenever there are duplicates.

In contrast, the DENSE_RANK column shows a value of 3 for the row directly after the duplicate rows. DENSE_RANK returns a value for a row that is one more than the number of distinct row values that precede it. There will never be gaps in the numbering sequence.

ROW_NUMBER returns a unique number for each row. For rows that contain duplicate values according to the specified ordering, the assignment of a row number is arbitrary; the row numbers could be assigned in a different order for the duplicate rows when the query is run another time.

Example: Ranking groups

Suppose that you want to find out which department has the highest average salary and the quantile of the average salary for each department. The following query groups the data by department, determines the average salary for each department, ranks the resulting averages, and shows a quantile for the average salary.

SELECT WORKDEPT, INT(AVG(SALARY)) AS AVERAGE, 
         RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_SALARY, 
         NTILE(3) OVER(ORDER BY AVG(SALARY) DESC) AS QUANTILE 
   FROM EMPLOYEE 
   GROUP BY  WORKDEPT 

This query returns the following information.

In this example, the NTILE function has an argument of 3, meaning that the results are to be grouped into 3 equal-sized sets. Since the result set is not evenly divisible by the number of quantiles, an additional row is included in each of the two lowest number quantiles.

Example: Ranking within a department

SELECT LASTNAME, WORKDEPT, BONUS, 
        DENSE_RANK() OVER(PARTITION BY WORKDEPT ORDER BY BONUS DESC) 
          AS BONUS_RANK_IN_DEPT 
   FROM EMPLOYEE
   WHERE WORKDEPT LIKE 'E%'

This query returns the following information.

Example: Ranking and ordering by table expression results

Suppose that you want to find the top five employees whose salaries are the highest along with their department names. The department name is in the department table, so a join operation is needed. Because ordering is already being done in the nested table expression, that ordering can also be used for determining the ROW_NUMBER value. The ORDER BY ORDER OF table clause is used to do this.

SELECT ROW_NUMBER() OVER(ORDER BY ORDER OF EMP),
             EMPNO, SALARY, DEPTNO, DEPTNAME      
   FROM (SELECT EMPNO, WORKDEPT, SALARY 
             FROM EMPLOYEE        
             ORDER BY SALARY DESC     
             FETCH FIRST 5 ROWS ONLY) EMP,
        DEPARTMENT 
   WHERE DEPTNO = WORKDEPT

This query returns the following information.

Example: Using OLAP Aggregates and CUME_DIST

Suppose that you want to find the rolling sum of the salaries for employees in department D11 and also the distribution of the salary.

SELECT ROW_NUMBER() OVER() AS ROW, LASTNAME, SALARY,
     SUM(SALARY) OVER(ORDER BY SALARY 
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_RANGE,
     SUM(SALARY) OVER(ORDER BY SALARY 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_ROWS,
     DECIMAL(CUME_DIST() OVER (ORDER BY SALARY),4,3) AS DISTRIBUTION
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

This query returns the following information.

This example shows two ways of defining the window to be used for calculating the value of a group.

The first way to define the window is with RANGE, which defines a group for all rows that have the same order by value. Row numbers 5 and 6 have the same salary value, so they are treated as a group. Their salaries are summed together and added to the previous total to generate the same value for each of the rows as seen in the ROLLING_TOTAL_RANGE column.

The second way to define the window is with ROWS, which treats each row as a group. In the ROLLING_TOTAL_ROWS column each row shows the sum calculated up to and including the current row. For rows that have the same salary value, such as rows 5 and 6, the order in which they are returned is not defined.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default aggregation group and could be omitted from the ROLLING_TOTAL_RANGE specification.

An ORDER BY is specified for the entire query to guarantee the rows are returned ordered.

Example: Using a RANGE windowing specification

Suppose you want to further analyze the salaries for DEPT D11.

This example groups salaries together by windowing, using a range to work with salary values 1000 less than or greater than each row's salary. It also returns which salary is the first value and the last value for the group used to calculate the sum.

SELECT LASTNAME, SALARY,
       SUM(SALARY) OVER(ORDER BY SALARY) AS ROLLING_TOTAL, 
       SUM(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS WINDOWED_TOTAL,
       FIRST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING),
       LAST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

This query returns the following information.

For each employee, a group is defined that contains all other employees in department D11 with salaries that fall within a range of 1000 below (PRECEDING) or 1000 above (FOLLOWING) that employee's salary. The WINDOWED_TOTAL column returns the sum of all the salaries in that group. The FIRST_VALUE column returns the lowest salary value that is part of the group. The LAST_VALUE column returns the highest salary value that is part of the group. Any employee that has a salary that is more than 1000 from the closest other salary is its own group.

An ORDER BY is specified for the entire query to guarantee the rows are returned ordered.

Example: Using a ROWS windowing specification

This example groups salaries together by windowing, using ROWS to work with salary values that are one row before and after each employee salary. The average of the 3 rows is returned.

SELECT LASTNAME, SALARY,
       DECIMAL(AVG(SALARY) OVER(ORDER BY SALARY 
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)7,2) AS AVG_SALARY
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

This query returns the following information.