NUMBER Function [Miscellaneous]

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

Syntax

NUMBER ( * )

Returns

INT

Usage

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.

Note: A syntax error is generated if you use NUMBER in a DELETE statement, WHERE clause, HAVING clause, ORDER BY clause, subquery, query involving aggregation, any constraint, GROUP BY, DISTINCT, a query containing UNION ALL, or a derived table.

Example

The following statement returns this numbered list:

number(*)
1
2
3
4
5

SELECT NUMBER( * )
FROM Departments
WHERE DepartmentID > 10

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.