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?

SQL Row Generator

Hello Guys,

Welcome to my blogs first quiz (or rather I will say Technique).

Row Generator is a very important SQL technique when writing complex queries.
It helps us to generate Rows from nowhere.

For example you need to create a dummy table for your sql practice.
What most of the people will do is to write a create table query and then run multiple insert statements
to populate some data. But Guys belive me there is a smarter way to do it.

Suppose you need a test table students to quickly tryout your hands on some query.
here is how you can do it ....

create table students is
select level STUDENT_ID ,
          dbms_random.string('A',5) STUDENT_NAME 
         ,Trunc(dbms_random.value(20, 100)) GRADE  
FROM dual connect by level < 100


This query create a table student with 100 Rows in it ..

There are various ways to generate rows from Nowhere.....

1. Using some Data Dictionary views 

There will mostly be enough rows in your database dictionary views which you can utilize to generate rows.

for ex

Select rownum  from all_tables where rownum < NUMOFROWS;

Drawback of this technique is that if you want to generate large no of rows (like 100+) you cannot 
guarantee that there will be enough rows in the (all_table) data dictionary. Also the performance
of generating rows this way is not good

2. Using Oracle CUBE function.

A Cube function generates 2power(N) no of rows with N parameters
Ex

SELECT ROWNUM FROM (
SELECT 1 FROM dual GROUP BY cube(1,2,3,4)
)

This query will generate 16 Rows.. So for generating say 100 Rows we can write query

SELECT rownum FROM (
SELECT 1 FROM dual GROUP BY cube(1,2,3,4,5,6,7) 
) where rownum <= 100

But the CUBE method has worst performance .

3. Using Connect BY

Connect By (Hierarchical) Query we can emulate the rows.

Ex
SELECT level FROM dual CONNECT BY LEVEL <= 100

Connect By is the best and most efficient way to generate Rows.

Thanks,
Idris