ROWNUM
is a pseudocolumn like oracle ROWNUM
. A pseudocolumn behaves like a table column, but is not actually stored in the table.
You can select from pseudocolumns, but you cannot insert, update, or delete their values.
A pseudocolumn is also similar to a function without arguments. However, functions without
arguments typically return the same value for every row in the result set, whereas
pseudocolumns typically return a different value for each row.
For each row returned by a query, the ROWNUM
pseudocolumn returns a number indicating the order in which LightDB selects the row
from a table or set of joined rows. The first row selected has a ROWNUM
of 1, the second has 2,and so on.
You can use ROWNUM
to limit the number of rows returned by a query, as in this example:
SELECT * from employees WHERE ROWNUM < 10;
If an ORDER BY
clause follows ROWNUM
in the same query, then the rows will be
reordered by the ORDER BY
clause.
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
If you embed the ORDER BY
clause in a subquery and place the ROWNUM
condition in the top-level query,
then you can force the ROWNUM
condition to be applied after the ordering of the rows.
For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
In the preceding example, the ROWNUM
values are those of the top-level SELECT
statement,
so they are generated after the rows have already been ordered by employee_id in the subquery.
Conditions testing for ROWNUM
values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM
of 1 and makes the condition false. The second row to be fetched is now
the first row and is also assigned a ROWNUM
of 1 and makes the condition false. All rows subsequently fail to satisfy the condition,
so no rows are returned.
You can also use ROWNUM
in UPDATE
and
DELETE
statements like SELECT
statement.
UPDATE employees SET employee_id = 0 WHERE ROWNUM < 10; DELETE FROM employees WHERE ROWNUM < 10;
Notice, ROWNUM
is not support canopy. If you sometimes referred to as top-N reporting in canopy,
consider using limit instead.