Pseudo columns in SELECT statement

SQL Guide : solidDB® SQL statements : Common clauses : Pseudo columns in SELECT statement

The pseudo columns described in this section 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 is supported:

SELECT * FROM (SELECT ROWNUM R, * FROM TABLE WHERE Y = 0) WHERE R = 5;