The ORDERBY_NUM() function is used with the ROWNUM() or INST_NUM() function to limit the number of result rows. The difference is that the ORDERBY_NUM() function is combined after the ORDER BY clause to give order to a result that has been already sorted.
That is, when retrieving only some of the result rows by using ROWNUM in a condition clause of the SELECT statement that includes the ORDER BY clause, ROWNUM is applied first and then group sorting by ORDER BY is performed. On the other hand, when retrieving only some of the result rows by using the ORDER_NUM() function, ROWNUM is applied to the result of sorting by ORDER BY.
FOR ORDERBY_NUM()
The following example shows how to retrieve athlete names ranked 3rd to 5th and their records in the history table (demodb).
--Ordering first and then limiting rows using FOR ORDERBY_NUM()
SELECT athlete, score FROM history
ORDER BY score FOR ORDERBY_NUM() BETWEEN 3 AND 5;
athlete score
============================================
'Luo Xuejuan' '01:07.0'
'Rodal Vebjorn' '01:43.0'
'Thorpe Ian' '01:45.0'
--Limiting rows first and then Ordering using ROWNUM
SELECT athlete, score FROM history
WHERE ROWNUM BETWEEN 3 AND 5 ORDER BY score;
athlete score
============================================
'Thorpe Ian' '01:45.0'
'Thorpe Ian' '03:41.0'
'Hackett Grant' '14:43.0'