ROWNUM - Limiting Results and limitations

Rownum is mostly used pseudocolumn column, used for limit result like LIMIT in other databases and also used for TOP N-query.

Some people used rownum in query and ask why it don't return exact result they want. Before using rownum we should really know the working of rownum.

WORKING
ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. A ROWNUM value is not assigned permanently to a row. 

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row: 

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure: 

select ..., ROWNUM
  from t
 where 
 group by 
having 
 order by ;

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
That is why a query in the following form is almost return wrong result:(emp list is not top most)

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

LIMITATION 
ROWNUM only work with < in where clause

ROWNUM can not used for range like if you need result between some range

SOLUTION TO THIS use INLINE QUERY

TOP N result: 

below query give us list of top paid employees 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;