Re: How to limit # of rows returned by SELECT

Screemin! wrote:
>
> Does anybody know if there is a way to limit the number of records
> returned by a SELECT statement? I have a large ODBC database
> where I would like to display the first 50 records that match a certain
> criteria, but I don't want the user to have to wait while the SELECT
> call processes all 7.2 quintillion records as it is a speed-critical
> application and has to happen in a couple of seconds.
>
> I tried SELECT *, COUNT(*) FROM TABLE WHERE COUNT(*) <=50
> but of course this didn't work, nor should it logically.
>
> Any help would be appreciated!
>
> Gordon Lawson, Senior Software Engineer
> Computers Unlimited
> Billings, MT
> (406) 255-9500
> gordon_at_cu.comp-unltd.com

I am not sure how the ODBC driver will impact this select statement. But in SQL*Plus the syntax would be

   SELECT *, COUNT(*) FROM TABLE WHERE ROWNUM <= 50;

If your are using the Cost-based optimizer than you may want to include an optimizer hint of /* FIRST_ROWS */. This will tell the optimizer to optimize to return the first row as quickly as possible. By default the optimizer will optimize to return all rows found as quickly as possible.

The select statement would look like:

    SELECT /* FIRST_ROWS */ *, COUNT(*) FROM TABLE WHERE ROWNUM <= 50;

Hope this helps.

Thomas Willingham
twilling_at_nsf.gov Received on Fri Feb 09 1996 - 00:00:00 CET