T-SQL Skip Take Stored Procedure
Solution 1
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 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
Solution 2
In addition to @Martin Smith's correct answer - when using a GROUP BY
, you can't use OFFSET-FETCH
without an ORDER BY
:
GROUP BY [cols]
ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
The following gives "incorrect syntaxt near 'OFFSET'" :
GROUP BY [cols]
--ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
Related videos on Youtube
Pete Davies
Updated on July 09, 2022Comments
-
Pete Davies almost 2 years
I don't seem to be having much luck on this site, still forever the optimist, I will keep trying. I have two tables, Journals and ArticleCategories that are joined using the this query:
SELECT Journals.JournalId, Journals.Year, Journals.Title, ArticleCategories.ItemText FROM Journals LEFT OUTER JOIN ArticleCategories ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
Can anyone tell me how I can re-write this make it into a Skip, Take query. In other words, I want to it skip the first n records and then take the next n. I think ROW_NUMBER is involved somewhere but I cannot work out how to use it in this case.
I suspect the reason why don't have much luck is that I find it difficult to explain what I am trying to do. If my question is not clear, please do not hesitate to tell me where I am going wrong and I will gladly try again. Perhaps I should also mention that I am trying to put this in a stored procedure. Many Thanks. Many thanks,
-
Martin Smith about 13 years
first n records
- as ordered by what? -
Pete Davies about 13 yearsGood point! I forgot to mention it needs to be ordered by something. Could I impose on you once again and ask how to include ORDER BY Journals.Year? Many thanks Martin.
-
Pete Davies about 13 yearsSorry about my previous comment Martin. If I had been more careful, I would have noticed you had included an ORDER BY statement. Thank you once again.
-
-
Pete Davies about 13 yearsOutstanding! Thanks Martin, that worked perfectly. Very grateful.