ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle 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. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
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 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
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.
See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about top-N queries
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 to assign unique values to each row of a table, as in this example:
UPDATE my_table
SET column1 = ROWNUM;
--------------------------------------------------------------------------------
Note:
Using ROWNUM in a query can affect view optimization. For more information, see Oracle9i Database Concepts.
--------------------------------------------------------------------------------
分享到:
相关推荐
oracle rownum和distinct
内涵oracle rownum的详细讲解。
oracle rownum
oracle 的rownum和咱们平时用的sqlserver的区别,其中好多的不一样!
oracle rownum 使用小技巧 里面包含分页
Oracle Rownum的使用与JSP分页显示的实现.pdf
oracle 分页查询 使用ROWNUM技巧及陷阱
ORACLE 中ROWNUM用法总结 整理版本,详细明了
对于 Oracle 的 rownum 问题,很多资料都说不支持>,>=,=,between...and,只能用以上符号(<、、!=),并非说用>,>=,=,between..and 时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来...
Oracle中rownum的使用
在本篇文章里小编给大家分享的是关于mysql类似oracle rownum写法以及相关实例内容,需要的朋友们可以学习下。
sql语句中select top n与oracle的rownum与mysql的limit 取前几条数据sql简单用法
关于oracle的rownum关于oracle的rownum关于oracle的rownum关于oracle的rownum关于oracle的rownum
Hint.pdf Oracle中rowid与rownum的使用.pdf Oracle优化 之 索引.pdf Oracle优化器.pdf STATSPACK详解.pdf 学用ORACLE_AWR和ASH特性.pdf 执行计划.pdf
主要介绍了ORACLE数据库中ROWNUM用法详解,对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,且rownum不能以任何表的名称作为前缀,需要的朋友们下面随着小编来一起学习学习吧
一、oracle中rownum 用于从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。 1、rownum 对于
主要介绍了Oracle中使用Rownum分页详细例子,本文将分别展示使用rownum伪列和row_number()分析函数来完成Oracle数据分页操作的具体使用方法,需要的朋友可以参考下
oracle rownum
oracle中rownum的用法及解说,主要说明了rownum的用法,让人看了很清楚.