Implement ROW_Number() to a Specific query

Question

I have the following query that returns a set of rows based on some input parameters :

  WITH hierarchy AS (
     SELECT yt.id    
       FROM [dbo].[TH_Categories] yt
      WHERE yt.ID = @topicID And CultureID = @cultureID 
     UNION ALL
     SELECT yt.id    
       FROM [dbo].[TH_Categories] yt
       JOIN hierarchy h ON h.ID = yt.ParentCategoryID)
    Select id, 
           [SiteName]
          ,[SiteURL]
          ,[Description]
          ,[Logo]
          ,[CultureID]
          ,[DateAdded],  
          dbo.GetSiteFollowers(id) AS Followers 
     from dbo.TH_Sites 
    where id in (Select Distinct SiteID 
                   from dbo.TH_CategoryFeeds 
                  Where CatID in (SELECT ID
                                    FROM hierarchy t ))

I am trying to implement paging using ROW_Number() function, but I am not able to find the way.

Any Help would be Highly appreciate it.

Answer 1

try this:

;WITH hierarchy AS 
(
     SELECT yt.id    
       FROM [dbo].[TH_Categories] yt
      WHERE yt.ID = @topicID And CultureID = @cultureID 
     UNION ALL
     SELECT yt.id    
       FROM [dbo].[TH_Categories] yt
       JOIN hierarchy h ON h.ID = yt.ParentCategoryID
)
, YourRows AS 
(
    Select id, 
           [SiteName]
          ,[SiteURL]
          ,[Description]
          ,[Logo]
          ,[CultureID]
          ,[DateAdded]
          ,dbo.GetSiteFollowers(id) AS Followers 
          ,ROW_NUMBER() OVER (ORDER BY ActivityDate DESC) AS RowNumber
     from dbo.TH_Sites 
    where id in (Select Distinct SiteID 
                   from dbo.TH_CategoryFeeds 
                  Where CatID in (SELECT ID
                                    FROM hierarchy t ))
)
SELECT * FROM YourRows 
WHERE RowNumber>=@Start and RowNumber<=@End

Answer 2

See SQL Server 2005 Paging – The Holy Grail for a good discussion of paging techniques.