Question:

What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

Answer:

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

Keywords:

© 2017 QuizBucket.org