Basic usage of Rownum in Oracle

2 years ago
William Carter
1 minute

在Oracle中，rownum是一个伪列，用于给查询结果集中的每一行分配一个唯一的行号。

The basic usage of rownum is as follows:

  1. row number
SELECT * 
FROM table_name
WHERE rownum <= 10;

The above example will return the first 10 rows of the result set.

  1. row number
SELECT * 
FROM (SELECT * FROM table_name ORDER BY column_name) 
WHERE rownum <= 10;

The above example will return the top 10 rows in the result set sorted by the specified column.

It’s important to note that rownum is assigned to rows before the query results are returned, so you cannot directly reference the rownum column in the SELECT statement for filtering conditions, or else an error will occur. If you need to filter by row number, you can use a subquery.

Also important to note is that rownum is assigned row numbers before the result set is returned, so when using rownum for filtering or sorting, it must be used within a subquery, otherwise the results may be incorrect.

bannerAds