ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.
ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.
Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:
SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal FROM EMPLOYEE o
| name | Sal | ROWNUM_BY_SAL |
|---|---|---|
| Hash | 100 | 1 |
| Robo | 100 | 2 |
| Anno | 80 | 3 |
| Darl | 80 | 4 |
| Tomiti | 70 | 5 |
| Pete | 70 | 6 |
| Bhuti | 60 | 7 |
| Meme | 60 | 8 |
| Inno | 50 | 9 |
| Privy | 50 | 10 |