[thelist] Fw: A Little Query Question

Joshua Olson joshua at waetech.com
Sat Dec 3 15:59:07 CST 2005

> -----Original Message-----
> From: Casey
> Sent: Friday, December 02, 2005 5:14 PM
> If you wanted to return the top 15 rows from a table, you 
> could say "SELECT 
> TOP 10 FROM table WHERE..."
> How would you word the query if you wanted rows 11 to 20?


Undoubtedly you will get more than a handful of ways to do this with a
single query, but I would propose that using a single query isn't going to
be the most scalable option.  Instead, I would suggest that you create a
stored procedure along the lines of the following:

Step 1.  Create a temporary table that can hold the PK of the dataset.
Ensure that at least one field in this temporary table is an identity (auto
incrementing) field.

	CREATE TABLE #temp (id int, row IDENTITY)

Step 2.  Insert into the temporary table with the conditional.

	INSERT INTO #temp (id) 
	FROM mytable
          WHERE [conditions]

Step 3.  Join the temp table against the original table to get the final
result set.  This time, use the WHERE clause to pull out the records you

SELECT [fields]
FROM mytable
ON #temp.id  = mytable.id
WHERE row > (@page - 1) * @recordsperpage
  AND row < @page * @recordsperpage

Step 4.  Delete the temp table.


For good measure, wrap this whole thing in a transaction.

Joshua L. Olson
WAE Tech Inc.
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:

More information about the thelist mailing list