Re: Limiting number of rows in result sets

Mark D. Stock wrote <3433A20D.90BD8D91@informix.com>...: >Art S. Kagel wrote: >> > > Mark D. Stock wrote: > > > > > > Randy Baker wrote: > > > > > > [SNIP problem description about limiting number off rows returned.] > > > > > 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. > >Not even then, because the resultant rows are sorted, not the scanned >rows. :-) > >You can use rowid, but not a good idea on fragmented tables. > >> 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. > >I know. As I said, there is no direct solution, but the SQL above might >provide a solution for certain cases. > >Any warranty, implied or expressed, was totally unintentional. ;-) > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >|Mark D. Stock - Informix SA http://www.informix.com |//////// /| >|mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| >| +-----------------------------------+//// / ///| >| 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.|// / /////| >|Cell: +27 83 250 2325 |Besides, Art will pick holes in it!|/ ////////| >+----------------------+-----------------------------------+-----------+ Much better (and faster on network) solution: Write a stored procedure that fetchs rows while counting them: CREATE PROCEDURE MySelect( rowcount INTEGER, <other parameters...> ) RETURNING <type1>, <type2>, ...DEFINE <ret1> <type1>; DEFINE <ret2> <type2>; ... DEFINE count INTEGER; LET count = 0; FOREACH SELECT <col1>, <col2>, ... INTO <ret1>, <ret2>, ... FROM ... RETURN <ret1>, <ret2>, ... WITH RESUME; LET count = count + 1; IF count > rowcount THEN EXIT FOREACH; END IF; END FOREACH; END PROCEDURE; Then use the following statement to select your data: EXECUTE PROCEDURE MySelect(<rowcount>, ...);instead of the embedded SELECT statement. Cursors in stored procedures are much faster than client-side cursors, because returned rows can be browsed by packets as with standard SELECT statements.