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

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.

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.