SELECT * FROM Company;It returns 100 rows. Can I just extract the range from 10-20?
I understand what you are trying to do, and it's quite common. However it is probably not as easy and straightforward as you may first think. A couple of issues to consider are:
First, there is no such thing as row numbers in SQL Server. Saying to SQL Server "give me the 10th row in this table" is meaningless.
Next, the resultset we retrieve using our SELECT statement isn't in a guaranteed order unless we explicitly apply an ORDER BY clause. Without an ORDER BY clause, a row may be retrieved as the tenth row the first time the SELECT statement is executed, and may be retrieved as the 20th row on a subsequent execution.
Fortunately SQL Server 2000 has the ability to stop executing a query after a certain number of rows have been retrieved. This is implemented as the TOP clause in a SELECT statement.
Now if we order our resultset by a unique table column, our resultset will have consistent ordering for every execution of the query. So by using the highest unique column value from the previous execution of the query as a starting point, we can tell SQL Server to retrieve a specific number of rows (using TOP) after the row with this value. Let's look at an example.
First thing we need to do is retrieve the TOP 10 rows from our table in order of CompanyId (producing rows 0-10 of our resultset):
SELECT TOP 10 * FROM Company ORDER BY CompanyIDMay produce a resultset
CompanyID CompanyName --------- ----------- 1 ABC Traders 2 BCD Traders 4 CDE Traders 6 DEF Traders 8 EFG Traders 11 FGH Traders 15 GHI Traders 34 HIJ Traders 36 IJK Traders 37 JKL TradersNow in our client application, we identify 37 as being our highest value for CompanyID retrieved, allowing us to subsequently execute the following
SELECT TOP 10 * FROM Company WHERE CompanyID > 37 ORDER BY CompanyIDto produce the 10th through to 20th rows of our resultset (in the order of CompanyId). This cycle can continue until the number of rows retrieved is less than the number requested by TOP, in which case you have come to the end of the resultset.
Going back to your original question, if you only specifically want rows 10 to 20 you could use a query similar to the following:
SELECT TOP 10 * FROM Company Where CompanyID NOT IN ( SELECT TOP 10 CompanyID From Company ORDER BY CompanyID ) ORDER BY CompanyIDThis query retrieves the next 10 rows that have CompanyID's greater than the CompanyID's of the first 10 rows.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2002