T-SQL Skip Take Stored Procedure

25,029

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 
Share:
25,029

Related videos on Youtube

Pete Davies
Author by

Pete Davies

Updated on July 09, 2022

Comments

  • Pete Davies
    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
      Martin Smith about 13 years
      first n records - as ordered by what?
    • Pete Davies
      Pete Davies about 13 years
      Good 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
      Pete Davies about 13 years
      Sorry 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
    Pete Davies about 13 years
    Outstanding! Thanks Martin, that worked perfectly. Very grateful.