Ask the Expert

Limiting the ranges of columns returned

How can I limit the range of columns in a return? For example,
 SELECT * FROM Company;
It returns 100 rows. Can I just extract the range from 10-20?

    Requires Free Membership to View

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 CompanyID
May 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 Traders
Now 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 CompanyID
to 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 CompanyID
This query retrieves the next 10 rows that have CompanyID's greater than the CompanyID's of the first 10 rows.

 

For More Information

This was first published in June 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: