Paging results from an SQL query can sometimes be quite difficult. MySQL has for a long time supported 'LIMIT x,y' to aid this, however with Microsoft SQL it has always been a bit more difficult. With MS SQL 2005 thing are a little easier. Try the snippet below, based on the AdventureWorks sample database included with SQL 2005.
PAGE_SIZE is the number of results you want per page
PAGE_COUNT is which page you want to display
SQL:
-
DECLARE @PAGE_SIZE int
-
SELECT @PAGE_SIZE = 15
-
DECLARE @PAGE_COUNT int
-
SELECT @PAGE_COUNT = 3
-
SELECT TOP (@PAGE_SIZE) * FROM (
-
SELECT EmployeeID, BirthDate,
-
ROW_NUMBER() OVER (ORDER BY BirthDate ASC) AS [Result Number]
-
FROM HumanResources.Employee) innerSel
-
WHERE [Result Number]> ((@PAGE_COUNT - 1) * @PAGE_SIZE)
-
ORDER BY [Result Number]
This entry was posted
on Wednesday, October 11th, 2006 at 11:28 am and is filed under Programming, Snippet.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
Leave a Comment