Re: Limiting number of rows in result sets

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. 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