Understanding Oracle ROWNUM in SQL

Rownum is a pseudocolumn in Oracle database that is used to return the sequential number of rows in the result set. It represents the row number of each row in the result set and will be sorted according to the query result order before returning the results. Rownum can only be used in the where clause and not in the order by clause.

When using rownum, it generates a unique row number on each row. This row number is assigned based on the order of the query results before they are returned, rather than the physical sorting of data in the table. Therefore, if you filter with rownum in a query, it assigns a row number to each row before applying the conditions.

For example, if you have a query like this:

SELECT * FROM employees WHERE rownum <= 5;

This query will return the top 5 rows of data in a specific order, regardless of the actual order of the data in the table. This means it is impossible to determine which rows will be returned unless a specific sorting condition is specified to ensure consistency.