Re: Next 33

"Michael Hill" <hillmw_at_ram.lmtas.lmco.com> wrote in message news:404926C0.64BFB1D7_at_ram.lmtas.lmco.com...
|
|
| "Mark C. Stock" wrote:
| >
| > "Michael Hill" <hillmw_at_ram.lmtas.lmco.com> wrote in message
| > news:40479F09.C62F9BE4_at_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 Received on Sat Mar 06 2004 - 20:59:22 CET