NUMBER function [Miscellaneous]

Generates numbers starting at 1 for each successive row in the results of the query.

NUMBER ( * )

The following statement returns this numbered list:

SELECT NUMBER( * )
FROM department
WHERE dept_id > 10

Use the NUMBER function only in a select list or a SET clause of an UPDATE statement. For example, the following statement updates each row of the seq_id column with a number 1 greater than the previous row. The number is applied in the order specified by the ORDER BY clause.

update empl
set seq_id = number(*)
order by empl_id

In an UPDATE statement, if the NUMBER(*) function is used in the SET clause and the FROM clause specifies a one-to-many join, NUMBER(*) generates unique numbers that increase, but may not increment sequentially due to row elimination.

NUMBER can also be used to generate primary keys when using the INSERT from SELECT statement, although using IDENTITY/AUTOINCREMENT is a preferred mechanism for generating sequential primary keys.