DB2: Automatic line numbering

When generating reports with lots of lines, sometimes it's nice to have them numbered automatically. For this DB2 offers the ROW_NUMBER() OVER() functionality. The first part numbers the rows in the order they are passed from the retrieving process. Therefore, if you're using the ORDER BY clause for displaying the resultset, you may get line numbers but in no special order. You'll need to pass the ordering directive to the second part OVER().

Here's an example:

Select
  ROW_NUMBER() OVER(order by NMBR asc) as ROW,
  NMBR
from
  lucky.numbers
order by
  NMBR asc

ROW                  NMBR
-------------------- ----------
                   1        123
                   2        456
                   3       2345
                   4      12345
 
  4 record(s) selected.