what if you wanted to get the “top” rows from an ordered result set? You would think the following query would work:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
The result may not be what you expected. The rows are sorted by SAL, but these rows are not the top
five salaries in the table. The result is correct once you understand that Oracle assigns ROWNUMs when a
row is first added to the result set. The ORDER BY clause is applied after the result set is collected, for
obvious reasons. So the ROWNUM is, in this case, like an artifact of the original retrieval.
You can still get the desired result by using ROWNUM and a subquery. The subquery returns the complete
result set in the proper order, and then you can use ROWNUM in the outer query to limit its result set. As following:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
This same approach is needed if you want to get any set of rows other than the “top” set. To get rows
from the middle of a result set, using the ROWNUM pseudocolumn, you might think that you could use the
following:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
This statement would not work, because ROWNUM is assigned as rows are returned to the initial result set. If a row does not remain in the result set because of the WHERE clause, it is discarded. This query
retrieves a row, assigns it ROWNUM 1, discards it because that ROWNUM does not fit the selection criteria, and then retrieves another row, which it reassigns the value of 1 for ROWNUM.
You can get the desired result by using the same type of subquery to retrieve the result set and then
imposing the selection criteria, as with the following:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
There is an analytic function called ROW_NUMBER() used to calculate row numbers, but this function can
include an ordering clause, as in the following SQL statement:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
This is pretty cool, but analytics are much more powerful than this simple example shows. The
ROW_NUMBER() analytic function can not only assign row numbers to a single ordered set of rows, but it
can be used to assign row numbers to rows while starting the numbering process over for specific
groups of row within the result set. Consider this SQL statement,
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Finally, you could combine this functionality in a subquery and use the outer query
to limit the retrieval of rows, as following: