[thelist] Fw: A Little Query Question

[Joshua Olson]

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

Casey,

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.

EG: 
	CREATE TABLE #temp (id int, row IDENTITY)

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

EG: 
	INSERT INTO #temp (id) 
	SELECT 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
want.

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

Step 4.  Delete the temp table.

DROP TABLE #temp

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

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/