Re: Limiting number of rows in result sets

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!|/ ////////| +----------------------+-----------------------------------+-----------+