Rownum between on SQL statement 1

[MikeJones]

Hi,

I'm trying to write a query that will reurn a subset of the total results, so at the end of the statement I have a...

and rownum between x and y

If I have x as any value other than 1 though it stops the query bringing back any results. For example

select * from dba_tables where rownum between 2 and 10

Returns nothing

But

select * from dba_tables where rownum between 1 and 10

returns stuff. Any ideas??

 

[tarko]

All the rows in your example will be the row number first,
and due to that Oracle will skip all the rows.

Perhaps you need Subqueries to workaround the problem.

 

[tarko]

select * from dba_tables where rownum between 1 and 10
minus
select * from dba_tables where rownum=1;

 

[Guest_imported]

select * from dba_tables where rownum between 1 and 10
minus
select * from dba_tables where rownum=1;

 

[MikeJones]

Nice one, I changed it to

select table_name, row_num
from (select table_name, rownum row_num
from dba_tables)
where row_num between 2 and 10

and it works fine. Thanks Tarko.

 

[MikeJones]

Hi again all I'm not sure either of the above will work as Oracle cannot garentee the order in which the rows will be returned. Can anyone confim or deny this for me??

I've changed the query slightly now to include an order by so it would now look like

select table_name, row_num
from (select table_name, rownum row_num
from dba_tables
order by table_name)
where row_num between 2 and 10


Thanks in advance.

Mike.

 

[HLF]

Mike, found this reference...

http://www.bf.rmit.edu.au/~orafaq/gloss_r.html#ROWID

ROWNUM is a pseudo-column returned as rows are selected and gives you a number for the row. &quot;Fine&quot;, say a lot of people, &quot;I will use it to peel off the first few rows by using (ROWNUM < n) in a WHERE clause&quot;. The problem with this is that ROWNUM is evaluated BEFORE the sorting (though after the WHERE). Try the following to see my point.

select ROWNUM, DEPTNO
from EMP
where ROWNUM < 10
order by DEPTNO, ROWNUM;

When you think about it for a minute or two, it cannot be any other way!

However, ROWNUM is good when you want to get a UNIQUE reference for each row returned, as long as you do not mind the order!

 

[tedball]

Hello,

I think I found a way to get around the rownum ordering problem, even though it's a little ugly.

Instead of

select table_name, row_num
from (select table_name, rownum row_num
from dba_tables
order by table_name)
where row_num between 2 and 10

try:

select table_name, row_num
from (select table_name, rownum row_num
from (select table_name
from dba_tables
order by table_name))
where row_num between 2 and 10

The innermost query gets the order, the next query assigns rownum, and the outermost allows selecting by the rownum.

I hope this helps!

What I want to know is if there is any way to do something like this in Sybase...

Thanks,
Michelle

 

[Turkbear]

Hi Folks,
You Can ONLY use < or <= when
comparing to ROWNUM


SQL> select empl_nm from hr_public
2 where rownum between 2 and 10;

no rows selected
;
1 select empl_nm from hr_public
2* where rownum = 10
SQL> /

no rows selected
;
1 select empl_nm from hr_public
2* where rownum <10
SQL> /

EMPL_NM
--------------------------------------------------
Graham,David D
DeSantiago,Anthony J
Heinlen,Richard H
Wikelius,Mark R
Hughes,Patrick Coover
Raati,Roberta A
Terry,Andrew W
Pearce,Dennis E
Fallon,Beverly E

9 rows selected.
;
1 select empl_nm from hr_public
2* where rownum <= 10
SQL> /

EMPL_NM
--------------------------------------------------
Graham,David D
DeSantiago,Anthony J
Heinlen,Richard H
Wikelius,Mark R
Hughes,Patrick Coover
Raati,Roberta A
Terry,Andrew W
Pearce,Dennis E
Fallon,Beverly E
Plutko,Ernest G

10 rows selected.

Also, as a previous post noted, the ROWNUM is assigned as the data is returned ( Pre-Sort ) and in Oracle there is no
way to know, in general, in what order the data is stored.

To get the 10th to 20th row of a sorted list you wil need a subquery:

Select * from (select empl_nm from hr_public order by empl_nm) where rownum < 21
minus
Select * from (select empl_nm from hr_public order by empl_nm) where rownum < 11
EMPL_NM
--------------------------------------------------
Abbe,Giles J
Abbott,Kevin E
Abear,Dale D
Abel,Elizabeth Jean
Abel,Jerome A
Abernathy,Jennifer Lynn
Abfalter,Robert A
Abrahamson,David A
Abrahamson,Duane E
Abu-shaqra,Diaa F.

10 rows selected.

SQL>
-------------------------------------------------

These are NOT, however, the 10th to 20th record as they are stored..That is indeterminate..( Affected by indexes, space available in tablespace, etc)

hth, ROWNUM has led many folks down a false path...