ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

NUMBER function [Miscellaneous]

Function 

Generates numbers starting at 1 for each successive row in the results of the query. NUMBER is primarily intended for use in select lists.

Syntax 

NUMBER ( * )

Usage 

You can use NUMBER(*) in a select list to provide a sequential numbering of the rows in the result set. NUMBER(*) returns the value of the ANSI row number of each result row. This means that NUMBER can return positive or negative values, depending on how the application scrolls through the result set. For insensitive cursors, the value of NUMBER(*) will always be positive because the entire result set is materialized at OPEN.

In addition, the row number may be subject to change for some cursor types. The value is fixed for insensitive cursors and scroll cursors. If there are concurrent updates, it may change for dynamic and sensitive cursors.

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.

NUMBER(*) can be used in a view (subject to the above restrictions), but the view column corresponding to the expression involving NUMBER(*) can be referenced at most once in the query or outer view, and the view cannot participate as a null-supplying table in a left outer join or full outer join.

In Embedded SQL, care should be exercised when using a cursor that references a query containing a NUMBER(*) function. In particular, this function returns negative numbers when a database cursor is positioned using relative to the end of the cursor (an absolute position with a negative offset).

You can use NUMBER in the right hand side of an assignment in the SET clause of an UPDATE statement. For example, SET x = NUMBER(*).

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

For information on AUTOINCREMENT, see CREATE TABLE statement.

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

  Behavior changes  The behavior of the NUMBER function changed in version 8. For more information, see Adaptive Server Anywhere behavior changes.

• Sybase    Not supported by Adaptive Server Enterprise.

Example 

The following statement returns a sequentially-numbered list of departments.

SELECT NUMBER( * ), dept_name
FROM department
WHERE dept_id > 5
ORDER BY dept_name