T-SQL Skip Take Stored Procedure

Question

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,

Answer 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  

Answer 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