Re: Limiting number of rows in a select...
Yes but that's very slow when you want more than 10 rows.
And this trick uses rowid's of a unique table. This can't apply
when you want the first rows that match a join condition.
Much faster is to open a cursor for the query and retreive
the rows until your count is reached. You can do that on the
client side, or in a SPL stored procedure using a FOREACH
cursor loop construct:
CREATE PROCEDURE pr1(v1 tv1, v2 tv2,...)
RETURNING t1,t2,t3...;DEFINE vrows INTEGER;
DEFINE r1 t1, r2 t2, r3 t3, ...;
...
LET vrows=0;
FOREACH
SELECT col1,col2,col3...
INTO r1, r2, r3, ...
FROM ...
WHERE...
LET vrows = vrows + 1;
RETURN r1, r2, r3, ... WITH RESUME;
IF vrows >= 10 THEN
EXIT FOREACH;
END IF;
END FOREACH;
END PROCEDURE;
Shame, Informix does not update the correct value of DBINFO("ROWCOUNT")
after closing a cursor loop (0 is always returned instead of the number of
rows read by the cursor, and within the loop 1 is returned after each
fetch). So you have to count rows yourself.
Using Oracle you would use the ROWNUM built-in function directly in the
WHERE clause of your query. Using Sybase, you would use the SET ROWCOUNT
option prior to execute the query (without forgetting to restore it to SET
ROWCOUNT 0 for unlimited queries).
Sujit Pal a 'crit dans l'article <5ssu2g$2l2@cssun.mathcs.emory.edu>...
>Mickey
>
>Heres a query that I use to get the first 10 rows from a table:
>
>SELECT * FROM tablename a
>WHERE 10 >
>(SELECT COUNT(*) FROM tablename b
>WHERE a.rowid > b.rowid)
>ORDER BY 1;>
>HTH
>
>Sujit Pal
>
>----------
>From: Mickey Mestel[SMTP:mickm@netcom.com]
>Sent: Tuesday, August 12, 1997 6:53 PM
>To: informix-list@rmy.emory.edu
>Subject: Q: Limiting number of rows in a select...
>
>hi,
>
> i know this has been posted before, but i can't find it. what is
>a good way to limit the number of rows returned by a select, like if you
>only want the first 10 or something?
>
> thanks,
>
> mickm
>
>--
>
>___________________________________________________________________________
__
>Mickey Mestel
mickm@netcom.com
>
>-on a beach in thailand to a beautiful, stoned, norwegian woman:
> "..yeah, it's just another foreign country without ice."
>---------------------------------------------------------------------------
--
>
>
>