ROWNUM in oracle?

ROWNUM in Oracle is a pseudo column which represent the selection order of the record selected from database by SELECT statement First row selected has rownum value 1, second row has 2 and so on.

This is mainly used to limit the rows in result set.

For example if you want to select only first 10 row from department table then you can write like

SELECT * FROM department WHERE ROWNUM <11;

Some time we use ROWNUM to assign a unique value to the records in a table like:

UPDATE department SET id = ROWNUM ;

You can also use this pseudo column in select list which will give you the order no of record selected, but if you have applied ORDER BY clause on the column, other than default order column, this will change the order of rownum column. This is because ROWNUM is assigned to every row while fetching from database where as ORDER BY is performed after that.

To get this difference no execute below statement and analyze the difference with earlier SQL

SELECT ROWNUM, A.* FROM department A WHERE ROWNUM <11 ORDER BY 3;

One question the interviewer always put related to this is :

What will be the out put of this SQL?

SELECT * FROM department WHERE ROWNUM > 1;

The answer is 0. Why?

Because while processing of SELECT statement first row processed by Oracle will be assigned ROWNUM 1 and So if we place the condition " rownum > any_number more than 0 " will be evaluated to false so will not be included in to result set, now second row become the first for selection and assigned ROWNUM value 1 which is also evaluate to false. Similarly all rows will evaluate to false.