Re: select and update by row number

-- 
David J Roth
(My own opinions)

Ari Kaplan <akaplan_at_interaccess.com> wrote in article
<58050a$3a7_at_thymaster.interaccess.com>...


> logicprobe_at_bbs.kis.net (Steve Crowe) writes:

> 

> >Hello,


 


> >I am taking a CMIS course on databases. I am tring to update and query

> >my database by row number. I have a table with 12,410 records in it. I

> >need to query the 1st,  the 6,205th, and the 12,410th rows. I tried

> >using RowNum in a where clause, but no dice. I also am thinking about


> >using RowID, but I am not sure how to go about this.


 


> >Thanks for any help,

> >Steve Crowe

> >logicprobe_at_bbs.kis.net

> 

> Steve,

> 

> Using the ROWID would be too complicated for what you wish to do. ROWID 

> uses codes for block id's and other "internal" numbering schemes.

> 

> Now, you need to determine what order the rows should be returned. That 

> is - what is the significance of being the 6,205th row. Is it an 


> alphabetical listing, etc? Without specifying an ORDER BY clause, it does


 


> not make much sense for the 6,205 and last rows...they will be returned 


> arbitrarily and may differ from query to query (without an ORDER BY


 clause)


> 

> Try something on the lines of:

> 

> SELECT * FROM table_name

> WHERE ROWNUM=1 or ROWNUM=6205 or ROWNUM=12410


> ORDER BY ordering_column;



NO! 

The rownum numbering is based on the query BEFORE the order by.

If you are using release 7.2 or later.
Assuming order_column is indexed.
Assuming all values in order_column are > 0.

select *
from (select * from table_name where order_column >0)
where rownum = 6205;




> 

> Best of luck!

> 

> -Ari Kaplan

> Independent Oracle DBA Consultant

> 

> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

> <-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->

> <->             email: akaplan_at_interaccess.com                    <->

> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

> 

>