[ACCEPTED]-T-SQL Skip Take Stored Procedure-pagination
Accepted answer
For 2005 / 2008 / 2008 R2
;WITH cte AS
(
SELECT Journals.JournalId,
Journals.Year,
Journals.Title,
ArticleCategories.ItemText,
ROW_NUMBER() OVER
(ORDER BY Journals.JournalId,ArticleCategories.ItemText) AS RN
FROM Journals LEFT OUTER JOIN
ArticleCategories
ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
)
SELECT JournalId,
Year,
Title,
ItemText
FROM cte
WHERE RN BETWEEN 11 AND 20
For 2012 this is 1 simpler
SELECT Journals.JournalId,
Journals.Year,
Journals.Title,
ArticleCategories.ItemText
FROM Journals
LEFT OUTER JOIN ArticleCategories
ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
ORDER BY Journals.JournalId,
ArticleCategories.ItemText
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
In addition to @Martin Smith's correct answer 3 - when using a GROUP BY, you can't use OFFSET-FETCH without 2 an ORDER BY:
GROUP BY [cols]
ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
The following gives "incorrect syntaxt 1 near 'OFFSET'" :
GROUP BY [cols]
--ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.