Paging in T-SQL

I am trying to implement paging using Row_Number() method in this query but with no luck.

The following Query uses recursive way to get Sites for a set of categories.

    WITH hierarchy AS (
  SELECT yt.id

    FROM [dbo].[TH_Categori开发者_如何学Ces] 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   ))

This query returns all the sites for a set of categories. I am trying to integrate Row_Number() to the dbo.TH_Sites table so I can get x number of records for each request using the

Where RowNumber BETWEEN @rowStart AND @rowEnd

But I keep getting T-SQL errors.

Any tips guys, thanks.

I am trying 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)

    Select id, [SiteName]
      ,[SiteURL]
      ,[Description]
      ,[Logo]
      ,[CultureID]
      ,[DateAdded], ROW_NUMBER() OVER (order by [DateAdded] DESC) AS 'RowNumber' ,  dbo.GetSiteFollowers(id) AS Followers from dbo.TH_Sites where RowNumber = 5  AND  id in (
 Select Distinct SiteID from dbo.TH_CategoryFeeds Where CatID in (
   SELECT ID
    FROM hierarchy t)) 

But I am getting RowNumber is not a valid column

Here is another Implementation :

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)
WITH numbered_hierarchy AS (
Select id, [SiteName]
  ,[SiteURL]
  ,[Description]
  ,[Logo]
  ,[CultureID]
  ,[DateAdded] , ROW_NUMBER() OVER (order by [DateAdded] DESC) AS 'RowNumber',  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 )) )

 SELECT id
     , [SiteName]
     , [SiteURL]
     , [Description]
     , [Logo]
     , [CultureID]
     , [DateAdded]
     , RowNumber
     , Followers
FROM numbered_hierarchy
WHERE RowNumber BETWEEN 1 AND 5

You need to put the ROW_NUMBER() function into your CTE:

WITH YourCTE AS
(
   SELECT (list of fields),
          ROW_NUMBER() OVER(ORDER BY ......) AS 'RowNum'
) 
SELECT (list of fields), RowNum
FROM YourCTE

Once you do that, you can easily select certain rows from the CTE:

WITH YourCTE AS
(
   SELECT (list of fields),
          ROW_NUMBER() OVER(ORDER BY ......) AS 'RowNum'
) 
SELECT (list of fields), RowNum
FROM YourCTE
WHERE RowNum BETWEEN 51 AND 75  -- or something like that

Not sure how that'll work with recursive CTE's though (I don't have SQL Server at hand right now to test this).

SQL doesn't allow the use of column aliases in a WHERE clause. Instead, put the RowNumber calculation into your CTE:

WITH numbered_rows AS (
    SELECT s.id
         , s.[SiteName]
         , s.[SiteURL]
         , s.[Description]
         , s.[Logo]
         , s.[CultureID]
         , s.[DateAdded]
         , ROW_NUMBER() OVER (order by s.[DateAdded] DESC) AS [RowNumber]
         , dbo.GetSiteFollowers(s.id) AS [Followers]
    FROM dbo.TH_Sites s
    INNER JOIN (
        SELECT DISTINCT SiteID
        FROM dbo.TH_CategoryFeeds cf
        INNER JOIN dbo.TH_Categories c ON c.ID = cf.CatID
        WHERE (c.ID = @topicID OR c.ParentCategoryID = @topicID)
            AND c.CultureID = @cultureID
    ) feeds ON feeds.SiteID = s.ID
)

SELECT id
     , [SiteName]
     , [SiteURL]
     , [Description]
     , [Logo]
     , [CultureID]
     , [DateAdded]
     , RowNumber
     , Followers
FROM numbered_rows
WHERE RowNumber BETWEEN @rowStart AND @rowEnd

EDIT: Eliminated the hierarchy CTE.

EDIT: Modified the JOINs to use a sub-query.

Edited: Added the distinct in the final select incase the join to cte was giving multiple records.

Try this, I think its what your going for.

;with hierarchy 
as
(
    select 
        yt.id

    from 
        dbo.TH_Categories yt

    where 
        yt.ID = @topicID 
        and CultureID = @cultureID 

    union

    select 
        parent.id as id

    from 
        dbo.TH_Categories yt
        left join dbo.th_categories parent
            on yt.id = parent.parentcategoryid
    order by dateadded desc
)

select distinct
    ths.id 
    ,ths.[SiteName]
    ,ths.[SiteURL]
    ,ths.[Description]
    ,ths.[Logo]
    ,ths.[CultureID]
    ,ths.[DateAdded],  
    dbo.GetSiteFollowers(ths.id) AS Followers 

from 
    dbo.TH_Sites ths
    join dbo.TH_CategoryFeeds cf
        on ths.id = cf.siteid
    join 
        (
            select
                id
                ,row_number() as RowNumber
            from
                hierarchy 
        ) h on cf.catid = h.id

where
    h.rownumber = 5;

This query returns all the sites :

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 just need to implement paging to this query. is it that difficult ?

Come on T-SQL GURUS !!!!!

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 )
)
AND ROW_NUMBER() OVER (ORDER BY [DateAdded] DESC) BETWEEN @rowStart AND @rowEnd
ORDER BY [DateAdded] DESC

;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 [DateAdded] 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>=@rowStart and RowNumber<=@rowEnd