Re: Setting maximum rows to be selected

In article <32h6ip$1rn_at_ionews.io.org> you wrote:
: I'm running PowerBuilder Enterprise with an Oracle 7.0 back end. What I
: want to know is if it's possible to limit the number of rows a select
: statement returns. For example, a fairly complex select returns order
: information based on several tables, a sub-select, and use of the
: group by statement. Out of the 100 or so rows returned, I would like to
: display the top 20 orders based on the revenue field (which is derived
: from a group sum expression).
 

: Neil Negandhi
: negandhi_at_io.org

As far as I know there is no way of doing it considering the fact that ur query is based on several tables.

A 'rownum <=20' condition will not produce the correct result as this rownum <=20 is evaluated before ur group by or order by clause. usually in a join between two tables the rownum<=20 condition is apllied on the "driving" table along with the other where clauses before a group by or order by.

I am not sure about the retriverow event or dbcancel( on a query based on multiple tables). Becuase in ur kind of requirement u need to retrieve all the rows satidfying the where condition and then select the top 20 or so. If u do a dbcancel after 20 rows.. well i am not sure whether this is what u want.

If the query was based on a single table and without any group by etc then there is a way of getting only the top 20 records in a order by using the hints in v7. But ofcourse such simplisistic requirements doesnot happen in real life.

Or if u can use a cursor and get all the records in the order u want and then display only the first 20. I don't know how/whether it can be done in PB as my PB knowledge is practicaly ZERO . But using oracle pre compilers these kind of things can be done. Received on Wed Aug 17 1994 - 01:52:23 CEST