Re: Setting maximum rows to be selected

In article <32h6ip$1rn_at_ionews.io.org> negandhi_at_io.org (Neil Negandhi) writes:

> 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

msallwas_at_world.nad.northrop.com (Michael Sallwasser) writes:

> In article <12a.4156.905_at_compudata.com> barry.roomberg_at_compudata.com
> (Barry Roomberg) writes:
 

>> Check into the "having" clause.  

> My knowledge is imperfect on this; however, I know of no method, with a
> SQL statement to acheive what you require.

I don't believe that this is all that difficult; how about

Select
 ... revenue ...
From
 ...
Where
 ...
 And 20 >= (Select Count(*)

            From
             ...
            Where
             ... much_repetition_of_previous_Where_clause ...
             And revenue < X.revenue ... )

If you e-mail me your Select statement, Neil, I'll modify it for you.

> (HAVING ain't gonna do it.)

This I do believe

> I have
> read that Oracle is considering adding that functionality. Whether that
> was a dream or will actually come to pass, I cannot say.
 

> I would like to add that my customers (in-house) would benefit from that
> functionality being added to SQL. Some are frustrated when I inform them
> that (what they see a simple) it is not possible to retrieve the top 5 only.

Don't inform them of that; in general, that ain't true :-)

> --
> ============================================================================
> Michael Sallwasser | Down one path is utter dispair and hopelessness. Down
> Northrop Grumman | the other is total destruction. Let us choose wisely.

Hank Robinson
Oracle DBA
University of Warwick Received on Wed Aug 17 1994 - 13:35:39 CEST