USING ROWNUM

1. ROWNUM gives numbering to the output of any query (Its like Serial No in any report/table).
   
2. ROWNUM Is a Psuedo Column generated(assigned) after predicate is executed but before sort/aggregate.

     Select rownum, ename from emp;
     ROWNUM     ENAME
          1             Ram
          2             John
          3             Amy

Here Rownum is generated after the query is executed.

Another Query with Order BY
     Select rownum, ename from emp order by ename;
     ROWNUM     ENAME
          3             Amy
          2             John
          1             Ram
As stated earlier , Rownum is assigned before sort/aggregate function , Hence in this case rownum is assigned to each row
before order by ename is executed. As a result the after order by rownum is messed up.

 3. Why Do I need to use ROWNUM?
Rownum is a very useful tool to restrict the no of rows fetched by a query. It can also be used for Numbering rows in a Report.

Suppose I need to fetch just 10 rows from Employees table.
I can use
select * from emp where rownum <= 10

I can use rownum for more complex queries like,
I need to find the TOP 3 Employees with Highest salary !!   

select * from (
select * from emp order by salary desc
) where rownum <=3

In this query, Inner query fetches all employees order by Salary in descending order. But as we just need TOP 3 employees
the outer query restricts the result to TOP 3 employees only.

4. How many records will ROWNUM = 2 Fetches
Ans : None
This is the most common misunderstanding with ROWNUM. While generating rownum its value is incremented only
after a successful output.

So a query
select * from emp where rownum = 2;

This will fetch 1st record assign it rownum 1 . But as the predicate is rownum = 2 this record will not be fetched.
The query will fetch 2nd record and assign it rownum 1 (As earlier record is not output yet). But again predicate
is rownum =2 so this record is also not fetched... and so on. Hence no record will be fetched with rownum =2 or 3...so on

What about rownum =1 ?
ie select * from emp where rownum =1;

This will fetch one record. WHY? read the last explanation again :)

Pls comment if you like/deslike this post .. any suggestions?