Will Clark wrote:
That's generally along the lines of what most folks do.
Thats a shame - I was hoping that there would be a way which didn't involve
running effectively the same SELECT twice on the table...
Hey Will!
I need that a lot for web frontend grid paging.
What I do is: First select only the PKs in question into a temp table or
table variable (with own PK) and make sure that sort order and
contraints are right.
Then select the required rows from the temp table and join to the
"production table" to get the fields you need.
Some example (northwind):
DECLARE @page int
DECLARE @pagesize int
SET @page = 3
SET @pagesize = 10
CREATE TABLE #pager (
OrderID int,
IDpager int identity(1,1)
)
INSERT INTO #pager (OrderID)
SELECT OrderID
FROM Orders
ORDER BY Freight DESC
SELECT IDPager, ShipName, ShipAddress, ShipCity
FROM Orders
INNER JOIN #pager ON Orders.OrderID = #pager.ORderID
WHERE IDPager BETWEEN ((@page-1)*@pagesize + 1) AND (@page*@pagesize)
DROP TABLE #pager
Daniel