Numbering the rows in a result table

Db2 does not number the rows in the result table for a query unless you explicitly request that the rows be numbered.

About this task

To number the rows in a result table, include the ROW_NUMBER specification in your query. If you want to ensure that the rows are in a particular order, include an ORDER BY clause after the OVER keyword. Otherwise, the rows are numbered in an arbitrary order.

Example

Suppose that you want a list of employees and salaries from department D11 in the sample EMP table. You can return a numbered list that is ordered by last name by submitting the following query:

SELECT ROW_NUMBER() OVER (ORDER BY LASTNAME) AS NUMBER,
WORKDEPT, LASTNAME, SALARY 
FROM DSN8910.EMP  
WHERE WORKDEPT='D11'

This query returns the following result:

---------+---------+---------+---------+---------+---------+-----
NUMBER                   WORKDEPT  LASTNAME              SALARY  
---------+---------+---------+---------+---------+---------+-----
                      1  D11       ADAMSON             25280.00  
                      2  D11       BROWN               27740.00  
                      3  D11       JOHN                29840.00  
                      4  D11       JONES               18270.00  
                      5  D11       LUTZ                29840.00  
                      6  D11       PIANKA              22250.00  
                      7  D11       SCOUTTEN            21340.00  
                      8  D11       STERN               32250.00
                      9  D11       WALKER              20450.00
                     10  D11       YAMAMOTO            24680.00
                     11  D11       YOSHIMURA           24680.00