ROWNUM

ROWNUM

 

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

 

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

 

SELECT * FROM employees

WHERE ROWNUM > 1;

 

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

 

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

 

UPDATE my_table

SET column1 = ROWNUM;

 

As per Ak Tom is mentioned below:

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 <where clause>

group by <columns>

having <having clause>

order by <columns>;

 

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.

 

The below mentioned query will fetch result :

select * from brand where rownum >= 1;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s