Using OLAP specifications

 

Online analytical processing (OLAP) specifications are used to return ranking numbers and row numbers for the result rows of a query. You can specify RANK, DENSE_RANK, and ROW_NUMBER.

 

Example: Ranking and row numbering

Suppose that you want a list of the top 10 salaries along with their ranking. The following query generates the ranking number for you:
 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.

Table 1. Results of the previous query
EMPNO SALARY RANK DENSE_RANK ROW_NUMBER
000010 52,750.00 1 1 1
000110 46,500.00 2 2 2
200010 46,500.00 2 2 3
000020 41,250.00 4 3 4
000050 40,175.00 5 4 5
000030 38,250.00 6 5 6
000070 36,170.00 7 6 7
000060 32,250.00 8 7 8
000220 29,840.00 9 8 9
200220 29,840.00 9 8 10

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. The following query groups the data by department, determines the average salary for each department, and ranks the resulting averages.
SELECT WORKDEPT, INT(AVG(SALARY)) AS AVERAGE, 
         RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_SALARY 
   FROM EMPLOYEE 
   GROUP BY  WORKDEPT 

This query returns the following information.

Table 2. Results of previous query
WORKDEPT AVERAGE AVG_SALARY
B01 41,250 1
A00 40,850 2
E01 40,175 3
C01 29,722 4
D21 25,668 5
D11 25,147 6
E21 24,086 7
E11 21,020 8

 

Example: Ranking within a department

Suppose that you want a list of employees along with how their bonus ranks within their department. Using the PARTITION BY clause, you can specify groups that are to be numbered separately.
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.

Table 3. Results of the previous query
LASTNAME WORKDEPT BONUS BONUS_RANK_in_DEPT
GEYER E01 800.00 1
HENDERSON E11 600.00 1
SCHNEIDER E11 500.00 2
SCHWARTZ E11 500.00 2
SMITH E11 400.00 3
PARKER E11 300.00 4
SETRIGHT E11 300.00 4
SPRINGER E11 300.00 4
SPENSER E21 500.00 1
LEE E21 500.00 1
GOUNOT E21 500.00 1
WONG E21 500.00 1
ALONZO E21 500.00 1
MENTA E21 400.00 2

 

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.

Table 4. Results of the previous query
ROW_NUMBER EMPNO SALARY DEPTNO DEPTNAME
1 000010 52,750.00 A00 SPIFFY COMPUTER SERVICE DIV.
2 000110 46,500.00 A00 SPIFFY COMPUTER SERVICE DIV.
3 200010 46,500.00 A00 SPIFFY COMPUTER SERVICE DIV.
4 000020 41,250.00 B01 PLANNING
5 000050 40,175.00 E01 SUPPORT SERVICES

 

Parent topic:

Retrieving data using the SELECT statement