ROWNUM 3

[arpan]

An Oracle database table has 6 records. Now when I execute the following command

SELECT * FROM tblTry WHERE ROWNUM<=4;

then, as expected, the 1st 4 records are displayed but when I execute the following query

SELECT * FROM tblTry WHERE ROWNUM>4;

then no records are displayed!! Shouldn't the last 2 records be displayed?

Thanks,

Arpan

 

[stefanhei]

Hi.
From Oracle-Doku:
...
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 use an inline-view for your second statement.

Stefan

 

[AOLEURO]

Hi

Yes it is correct.

ROWNUM is a line counter on selected rows – not “founded” rows by WHERE clause.

So you can say – I want to see lines until count of 4

But you can’t say – I want to see lines above count of 4 – because the first showed line is number one – so oracle walks through the founded rows – but can never show you lines because each time the line is one like this:


Found rows from where clause (without looking at ROWNUM)
Want to show the first row – get next line counter (rownum) (= 1)
How is ROWNUM in WHERE clause ( > 4 )
Ok – not good – drop the row
Get next founded row
Want to show the row – get next line counter (rownum) (= 1)
How is ROWNUM in WHERE clause ( > 4 )
Ok – not good – drop the row
…
…
…
…



Oracle do not USE the rownum in the where clause on finding the rows but on showing/selecting the rows.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[arpan]

Hi Stefan,

Thanks for your reply. But why FALSE is always returned when ROWNUM is greater than any positive integer in the WHERE clause. Also why do the following 2 queries retrieve all the rows from a database table?

SELECT * FROM Try WHERE ROWNUM>0

&

SELECT * FROM Try WHERE ROWNUM>-1

Actually if -1 is replaced by any other number lesser than zero (even floats like -200.52) then also all the rows are retrieved! Why so?

Thanks once again & thanks to Allan as well.

Regards,

Arpan

 

[AOLEURO]

Hi

See my reply


Line COUNT = 1 is greater than -200.52 !

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[stefanhei]

Your query:
SELECT * FROM tblTry WHERE ROWNUM>4;
Oracle tries to read a row. The ROWNUM for the first row is assumed 1. Your WHERE-clause evaluates to 1>4 => FALSE.
The same is true for the next row and all other rows.

So if the value in your WHERE-clause is negative, evaluation is TRUE for all records.

Stefan

 

[Turkbear]

If you really want to use ROWNUM as a where condition
( altho' its not much use except to limit the size of the result set - it cannot be used, for instance, to get a TOP-N type result, at least not in the way you might think) and want to do what you tried in your
second query [rownum> 4 to get 2 records - tho' maybe not the LAST 2 records, since there is no such thing in Oracle as FIRST or LAST] you can do:

select * from tblTry
MINUS
select * from tblTry where rownum <= 4

 

[ManjulaMadhu]

Arpan,

If you are looking to select rows N through M then try this:

select *
from ( select a.* rnum
from ( select * from tbl1 order by col1) a
where rownum <= max_rows )
where rnum >= min_rows

 

[ManjulaMadhu]

where max_rows and min_rows are the rownumbers (eg: If you want to select from 4 to 10 rows then min_rows - 4 and max_rows- 10 )