By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

Next 33

P: n/a
I have some query like:

select my_field from my_table

and there are 500000 rows in the table, how do I tell Oracle to display
the next 33?

i tried select my_field from my_table next 33 and it doesn't like it.

Mike
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
news:40***************@ram.lmtas.lmco.com...
| I have some query like:
|
| select my_field from my_table
|
| and there are 500000 rows in the table, how do I tell Oracle to display
| the next 33?
|
| i tried select my_field from my_table next 33 and it doesn't like it.
|
| Mike

are you familiar with the WHERE clause?

what version of oracle are you using?

what is your interface (tool)?

;-{ mcs
Jul 19 '05 #2

P: n/a


"Mark C. Stock" wrote:

"Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
news:40***************@ram.lmtas.lmco.com...
| I have some query like:
|
| select my_field from my_table
|
| and there are 500000 rows in the table, how do I tell Oracle to display
| the next 33?
|
| i tried select my_field from my_table next 33 and it doesn't like it.
|
| Mike

are you familiar with the WHERE clause?

yes and so using the where clause gets me from 500000 rows to 25000
what version of oracle are you using?

Dont know for sure at least 8. something I think.
what is your interface (tool)?

I am using perl dbi and so I'd like to limit the number number of
records using the query statement. Some tools do that for you as does
coldfusion.
;-{ mcs

Jul 19 '05 #3

P: n/a
"Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
news:40***************@ram.lmtas.lmco.com...
|
|
| "Mark C. Stock" wrote:
| >
| > "Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
| > news:40***************@ram.lmtas.lmco.com...
| > | I have some query like:
| > |
| > | select my_field from my_table
| > |
| > | and there are 500000 rows in the table, how do I tell Oracle to
display
| > | the next 33?
| > |
| > | i tried select my_field from my_table next 33 and it doesn't like it.
| > |
| > | Mike
| >
| > are you familiar with the WHERE clause?
| >
|
| yes and so using the where clause gets me from 500000 rows to 25000
|
| > what version of oracle are you using?
| >
|
| Dont know for sure at least 8. something I think.
|
| > what is your interface (tool)?
| >
|
| I am using perl dbi and so I'd like to limit the number number of
| records using the query statement. Some tools do that for you as does
| coldfusion.
|
| > ;-{ mcs

to get version:

select * From v$version
to return a 'pageful' of rows, do something like this:

-- filter by rownum range (not possible in query that selects the rownum)
select uu.*
from (
-- add rownum after the order by is performed (assigned in order row is
processed)
select rownum as therownum, u.*
from (
-- the real query
select username
from all_users
order by username
) u
where rownum <= :endrec
) uu
where uu.therownum >= :startrec
it would be tempting to try this:

select username
from all_users
order by username
where rownum between :startrec and :endrec

but that doesn't work because a) rownum is assigned prior to the sort and b)
a where clause predicate cannot filter rows based on a (locally assigned)
rownum being greater than a known value, because the rownum starts at 1 and
is only incremented as rows are added to the resultset -- the first row
returned would have a rownum of 1, if it is rejected, rownum does not
increment, so the 2nd row examined would have a rownum of 1, etc.

so, the select with order by is used as an inline view (from-clause
subquery) and gets the rownum assigned as rows are retrieved (ordered) from
the inline view, stopping at the specified endrec. this is then used as an
inline view to another query that discards all rows prior to the specified
startrec

;-{ mcs
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.