What does rownum mean in oracle

The rownum pseudo-column in Oracle returns the row number of the currently selected row, starting from 1 and increasing sequentially. Its usage scenarios include: limiting the number of returned rows, obtaining row numbers, paging, and performing row-level operations. It should be noted that rownum is only applicable to SELECT queries and cannot be updated.

Use of rownum in oracle

What is rownum?

rownum in Oracle is a special pseudo column that returns the row number of the currently selected row. Line numbers start at 1 and increase sequentially on each line.

Syntax of rownum

SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS rownum

Where:

• column_name is the column used to sort the rows.
• The OVER (ORDER BY column_name) clause specifies the sort order.

rownum usage scenarios

rownum can be used in a variety of scenarios, including:

• Limit the returned rows Number: Filter out a specific range of rows by using rownum, for example: sqlSELECT * FROM table_name WHERE rownum <= 10;
• ##Row number: Get the row number of the currently selected row, for example: sqlSELECT rownum FROM table_name WHERE id = 1;
• Paging: Combine the limit and offset clauses for paging, for example: sqlSELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 0;
• Row-level operations: Perform operations on specific rows, such as update or delete, for example: sqlUPDATE table_name SET name = 'John' WHERE rownum = 1;

Note

rownum only applies to SELECT queries.
• rownum is not updatable, once queried, it will not change.
• When using rownum, make sure the query has the appropriate sort order to get the expected results.

The above is the detailed content of What does rownum mean in oracle. For more information, please follow other related articles on the PHP Chinese website!