What is the difference between ROWNUM
and ROW_NUMBER
?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- php PDO::FETCH_ASSOC doesnt detect select after ba
- Bulk update SQL Server C#
From a little reading, ROWNUM is a value automatically assigned by Oracle to a rowset (prior to ORDER BY being evaluated, so don't ever
ORDER BY ROWNUM
or use aWHERE ROWNUM < 10
with anORDER BY
).ROW_NUMBER() appears to be a function for assigning row numbers to a result set returned by a subquery or partition.
Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of
ROWNUM
compared toROW_NUMBER() OVER()
:http://www.inf.unideb.hu/~gabora/pagination/results.html
Rownum starts with 1 ..increases after condition evaluated results to true . Hence rownum >=1 returns all rows in table
rownum is a pseudocolumn which can be added to any select query, to number the rows returned (starting with 1). They are ordered according to when they were identified as being part of the final result set. (#ref)
row_number is an analytic's function, which can be used to number the rows returned by the query in an order mandated by the row_number() function.
ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query:
ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows: