Friday, March 6, 2015

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.

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
 group by 
 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;
ROWNUM only work with < in where clause
ROWNUM can not used for range like if you need result between some range

TOP N result: 
below query give us list of top paid employees 
select *
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

No comments:

Post a Comment

web stats