25.10.2007

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.

Diese Seite zu Mister Wong hinzufügen

Keine Kommentare: