oracle tip 1 -- rownum, row_number()

what if you wanted to get the “top” rows from an ordered result set? You would think the following query would work: 

SELECT ROWNUM, ENAME, SAL FROM SCOTT.EMP WHERE ROWNUM < 6 ORDER BY SAL DESC;

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:

SELECT ROWNUM, ENAME, SAL FROM 
        ( SELECT  ENAME, SAL FROM SCOTT.EMP ORDER BY SAL DESC )
    WHERE ROWNUM < 6;

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:

SELECT ENAME, SAL FROM SCOTT.EMP
    WHERE (ROWNUM > 3 AND ROWNUM < 7)
    ORDER BY SAL

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:

SELECT ENAME, SAL FROM
    (SELECT ENAME, SAL, ROWNUM R FROM SCOTT.EMP
    ORDER BY R)
    WHERE (R > 3 AND R < 7);

 

 

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:

SELECT ENAME, SAL,
        ROW_NUMBER() OVER (ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;

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,

SELECT ENAME, SAL,
    ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER
FROM SCOTT.EMP;

Finally, you could combine this functionality in a subquery and use the outer query
to limit the retrieval of rows, as following: