Rownum

Description

The Rownum operator generates a pseudocolumn that returns a number indicating the row number of the result obtained from the query. The value of Rownum in the first row is 1. You can use the keyword Rownum to filter the row number in the query result. It usually appears in the ROWNUM clause.

The restrictions on using ROWNUM are as follows:

• Do not use ROWNUM as an alias to avoid ambiguity in SQL statements.
• Do not use ROWNUM when creating an index.
• Do not use ROWNUM as the default value when creating a table.
• Do not use ROWNUM as an alias in the WHERE clause.
• Do not use ROWNUM when inserting data.
• Do not use ROWNUM in a tableless query.
• Do not use ROWNUM in the LIMIT clause.
• Do not use ROWNUM as a parameter of the EXECUTE statement.
• Do not use ROWNUM to update a clause in the UPSERT statement.

Typical Scenarios

The filter condition statement contains ROWNUM.

Examples

Example: The filter condition statement contains ROWNUM.

-- Prepare data.
gaussdb=# CREATE TABLE t1 ( id INT , number INT );
CREATE TABLE
gaussdb=# INSERT INTo t1 VALUES(generate_series(1,50), 1);
INSERT 0 50
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,50), 2);
INSERT 0 50 

-- Execution result.
gaussdb=# EXPLAIN SELECT t1.id FROM t1 WHERE rownum > 10;
                        QUERY PLAN                        
----------------------------------------------------------
 Rownum  (cost=0.00..2.50 rows=150 width=4)
   Filter: (ROWNUM > 10)
   ->  Seq Scan on t1  (cost=0.00..2.50 rows=150 width=4)
(3 rows)

-- Drop.
gaussdb=# DROP TABLE t1;

In the preceding example, the output of the Rownum operator is as follows.