CODEWITHSUNDEEP
sqlsql-serversql-server-2008
neojakey
neojakey

Reputation: 1663

Pagination and Grouping in SQL

I am trying to write an SQL script, but I am getting unexpected results. The @TotalResults give me 6 as the records, when I know there are 33 records returned.

Here's the code:

SELECT @TotalPages = CEILING(COUNT(a.MemberID)/@PageSize), @TotalResults = COUNT(a.MemberID)
FROM Member a
INNER JOIN MemberBusinessCat b ON b.MemberID = a.MemberID
INNER JOIN BusinessCat c ON c.BusinessCatID = b.BusinessCatID
WHERE a.SystemID = @SystemID
AND c.CategoryName LIKE '%' + @SearchStr + '%'
AND ( @ShowUnclaimed != 'N' 
  OR ( a.Claimed = 'Y' AND a.SBIcon = 'N' )
)
AND a.Viewable = 'Y'
GROUP BY a.MemberID, a.CreateDate, a.UserName, a.PrCity, a.MemberDisplayName, a.PrStateID, a.PrPhone, a.ShortDesc, a.PrCountryID;

WITH CoalPrepCategorySearch AS
(
     SELECT ROW_NUMBER() OVER(ORDER BY a.MemberDisplayName ASC) AS RowNum,
     a.MemberID,
     a.UserName,
     a.PrCity,
     a.PrStateID,
     a.PrPhone,
     @TotalPages AS TotalPages,
     a.MemberDisplayName AS DisplayName,
     a.ShortDesc,
     @TotalResults AS TotalResults,
     a.PrCountryID
     FROM Member a
     INNER JOIN MemberBusinessCat b ON b.MemberID = a.MemberID
     INNER JOIN BusinessCat c ON c.BusinessCatID = b.BusinessCatID
     WHERE a.SystemID = @SystemID
     AND c.CategoryName LIKE '%' + @SearchStr + '%'
     AND ( @ShowUnclaimed != 'N' 
       OR ( a.Claimed = 'Y' AND a.SBIcon = 'N' )
     )
     AND a.Viewable = 'Y'
     GROUP BY a.MemberID, a.CreateDate, a.UserName, a.PrCity, a.MemberDisplayName, a.PrStateID, a.PrPhone, a.ShortDesc, a.PrCountryID
)
SELECT * 
FROM CoalPrepCategorySearch
WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize
ORDER BY DisplayName ASC

I am pretty sure it is related to the grouping. If it is, then how can I get the total results? What am I doing wrong?

Many thanks in advance.

neojakey

Upvotes: 0

Views: 95

Answers (1)

Devart
Devart

Reputation: 122002

Possible this be helpful for you -

;WITH cte AS 
(
    SELECT a.*
    FROM dbo.Member a
    JOIN dbo.MemberBusinessCat b ON b.MemberID = a.MemberID
    JOIN dbo.BusinessCat c ON c.BusinessCatID = b.BusinessCatID
    WHERE a.SystemID = @SystemID
        AND c.CategoryName LIKE '%' + @SearchStr + '%'
        AND a.Viewable = 'Y'
        AND (
                  @ShowUnclaimed != 'N' 
                OR 
                  a.Claimed + a.SBIcon = 'YN' 
        )
), CoalPrepCategorySearch AS
(
     SELECT 
         ROW_NUMBER() OVER(ORDER BY a.MemberDisplayName ASC) AS RowNum,
         a.MemberID,
         a.UserName,
         a.PrCity,
         a.PrStateID,
         a.PrPhone,
         a.MemberDisplayName AS DisplayName,
         a.ShortDesc,
         a.PrCountryID
    FROM (
        SELECT DISTINCT 
            a.MemberDisplayName,
            a.MemberID,
            a.UserName,
            a.PrCity,
            a.PrStateID,
            a.PrPhone,
            a.ShortDesc,
            a.PrCountryID
        FROM cte a
    ) a
)
SELECT *
FROM CoalPrepCategorySearch t
CROSS JOIN (
    SELECT 
          TotalPages = CEILING(COUNT(t2.MemberID) / @PageSize)
        , TotalResults = COUNT(t2.MemberID) 
    FROM cte t2
    GROUP BY t2.MemberID
) t2
WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize --??
ORDER BY t.DisplayName

Upvotes: 2

Related Questions