In week 51/2011 I wrote a tip about Numbering of returned rows from SQL statement. At that time I missed a function just doing it. Actually it was already present in DB2 version 9, but hidden away as a socalled OLAP specification. I discovered this recently, at last.

Here is a revised version of the example from week 51/2011:

SELECT ROWNUMBER() OVER(), X.*
FROM SYSIBM.SYSDATABASE X
WHERE NAME LIKE 'DSN%'

ROWNUMBER() OVER() is not handicapped by the disadvantages of using a SEQUENCE. The first row gets the number 1 and so forth every time. There are no limitations regarding ORDER BY, GROUP BY or DISTINCT. In other words ROWNUMBER() OVER() works exactly as a function to number returned rows should work.

By the way OLAP specifications may be used to assign returned rows individual rankings. You can divide rows in groups and assign ranks within each group. If two rows deserve the same rank you can assign them the same rank or disregard ranking. In DB2 10 more functions that you might find useful have been put into the OLAP specifications.