The pseudo columns described in this topic can be used in the select‑list of a SELECT statement.
Pseudo column
Type
Explanation
ROWVER
VARBINARY(10)
Version of the row in a table.
Because ROWVER refers to a single row, it can only be used with queries that return rows from a single table.
ROWID
VARBINARY(254)
Persistent id for a row in a table.
Because ROWID refers to a single row, it can only be used with queries that return rows from a single table.
ROWNUM
DECIMAL(16,2)
Row number indicates the sequence in which a row was selected from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second row has 2, and so on.
Because ROWNUM is given to a row before the order by clause is evaluated, you cannot use ROWNUM to identify sorted rows.
ROWNUM is chiefly useful for limiting the number of rows returned by a query for example, WHERE ROWNUM < 10.
Note You cannot use the following constructions:
▪ ROWNUM = n
▪ ROWNUM ? n
▪ ROWNUM IN (n)
For example, the following constructions are not supported:
▪ ROWNUM = 2
▪ ROWNUM IN (2,4,6)
Instead, use the constructions <, >, ?, ?, or BETWEEN.
For example:
SELECT * FROM TABLE1 WHERE Y = 0 AND ROWNUM < 2;
SELECT * FROM TABLE1 WHERE Y = 0 AND (ROWNUM > 1 AND ROWNUM < 3);
SELECT * FROM TABLE1 WHERE Y = 0 AND ROWNUM BETWEEN 1 AND 3;
Additionally, comparing a ROWNUM condition to a result set of a subquery is not supported.
For example, the following construction is not supported:
SELECT * FROM TABLE1 WHERE Y = 0 AND ROWNUM < (SELECT COUNT(*) FROM TABLE1 WHERE Y = 0);
ROWNUM can, however, be used within a subquery. For example, the following construction is supported:
SELECT * FROM (SELECT ROWNUM R, * FROM TABLE WHERE Y = 0) WHERE R = 5;