Paging in T-SQL

Question

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_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   ))

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

Answer 1

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).

Answer 2

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.

Answer 3

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;

Answer 4

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 !!!!!

Answer 5

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

Answer 6

;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