Limiting number of rows in result sets

Limiting number of rows in result sets

Post by Randy Bake » Sun, 31 Dec 1899 09:00:00



I have a customer who is interested in running our ODBC-based application
on Informix. The application was originally targeted for SQL Server, and
uses the SQL Server specific feaure "SET ROWCOUNT n" to cap the number of
rows returned or processed by a query in some situations.

Does Informix support a comparable feature?

--
Randy Baker (remove Z from address in email replies)

 
 
 

Limiting number of rows in result sets

Post by Billy Wheele » Sun, 31 Dec 1899 09:00:00



Quote:> I have a customer who is interested in running our ODBC-based
> application on Informix. The application was originally targeted for
> SQL Server, and uses the SQL Server specific feaure "SET ROWCOUNT n"
> to cap the number of rows returned or processed by a query in some
> situations.

> Does Informix support a comparable feature?

It is being considered for OnLine 7.3. It is already in XPS, but you
may not want to spend around $500 000 just for that feature... :-)

--
Ciao,
Billy

Nose to the ear in Johannesburg -- * City never looked so good!
For a good time, call: http://www.veryComputer.com/

I installed a photo of Princess Di as "wallpaper" on my PC, just as
kind of a tribute or memorial or something.  Looks nice, but every
time I try and take a screen shot, my machine crashes.

 
 
 

Limiting number of rows in result sets

Post by Mark D. Stoc » Sun, 31 Dec 1899 09:00:00



> I have a customer who is interested in running our ODBC-based
> application
> on Informix. The application was originally targeted for SQL Server,
> and
> uses the SQL Server specific feaure "SET ROWCOUNT n" to cap the number
> of
> rows returned or processed by a query in some situations.

> Does Informix support a comparable feature?

Not directly, but try:

        SELECT  cols
        FROM    table
        WHERE   no_rows >    (
                                SELECT  COUNT(*)
                                FROM    same_table
                                WHERE   same_table.primary_key <
                                                table.primary_key
                                )

where no_rows is the number of rows to return.

The following returns the first ten customer numbers from the customer
table:

        SELECT  customer_num
        FROM    customer
        WHERE   10 > (
                        SELECT  COUNT(*)
                        FROM    customer c
                        WHERE   c.customer_num < customer.customer_num
                        )

Hope that helps,
--
Mark.

+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA       http://www.informix.com |////////  /|

|                      +-----------------------------------+////  / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain.  |///  / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|//  / /////|

+----------------------+-----------------------------------+-----------+

 
 
 

Limiting number of rows in result sets

Post by Art S. Kage » Sun, 31 Dec 1899 09:00:00





[SNIP problem description about limiting number off rows returned.]

Quote:>         SELECT  cols
>         FROM    table
>         WHERE   no_rows >       (
>                                 SELECT  COUNT(*)
>                                 FROM    same_table
>                                 WHERE   same_table.primary_key <
>                                                 table.primary_key
>                                 )

> where no_rows is the number of rows to return.

> The following returns the first ten customer numbers from the customer
> table:

>         SELECT  customer_num
>         FROM    customer
>         WHERE   10 >    (
>                         SELECT  COUNT(*)
>                         FROM    customer c
>                         WHERE   c.customer_num < customer.customer_num
>                         )

Only if you add an ORDER BY customer_num clause to the outer select
otherwise if the first customer_num returned is the 10,000th you'll only
get that one back.  This is a very special case, Mark, it does not
handle
fetching n rows out of m rows with identical keys, etc.  Even this case
gets hinky and slow if you want the 10 rows starting with the Nth row
then you either need two correllated subqueries and lots of patience or
you cannot do this at all.

Art S. Kagel

 
 
 

1. Limiting SQL Result Sets by number of Records

I know this is probably not adhering to proper form, but I need help.  I am
trying to limit the number of records in the result set by a number of
records count.  Is there a way to do this?

My problem is we host the main database file (dbase) on the server.  We
don't have SQL Server.  We have just added a bunch of code using the Query
component.  It worked find with a database of 30,000 records.  When we
tested it on 300,000 it took about 15 minutes to return a result set.
Yikes!  I simple solution was to limit the number of records returned.  Any
other suggestions.

Thanks - any and all comments welcomed.

Brian Laughlin

2. Single quotes in SQL statement problem

3. Limiting result set size to arbitrary number

4. Urgent: Upgradring database to new versions

5. Help - Setting number of rows limit

6. MSDE Server: Computer name change

7. The number of rows with pending changes has exceeded the set limit

8. copy between 2 rept fields

9. Limiting number of rows in answer set

10. Add Row Number to Result Set

11. Numbering rows in a result set

12. how to slice the result set by row number in SQL 7.0