Get total count before getting a subset from numbered results

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    I am returning a subset of search results (based on row_number()) but although I'm returning only, let's say, 10 rows, I need to also return the total count (grand total) that the search criteria would return.  Another team has the same requirement, and they are ultimately calling their stored procedure TWICE.  Once to return the subset of results for populating the grid, and then a second time where they don't limit the number of results that come back so that they can return a total.  This does not seem like an ideal solution to me.  Here is some pseudocode of what I've landed on, but would like to know if anyone has a better suggestion.

    ; WITH LotsOfData AS
    (SELECT RandomID = CHECKSUM(NEWID()),
       RowNum = ROW_NUMBER() OVER (ORDER BY CHECKSUM(NEWID()))
    FROM
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T1(c1)
    CROSS JOIN
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T2(c2)
    CROSS JOIN
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T3(c3)
    )

    SELECT RandomID, RowNum,
       /* This is my attempt, but I know I am running the cte again to get this:*/
       (SELECT MAX(RowNum) FROM LotsOfData) AS 'TotalResults'
    FROM LotsOfData
    WHERE RowNum BETWEEN 50 AND 59

    Thank you!

  • Joe Torre

    SSChampion

    Points: 10239

    Try:

    ;
    WITH    LotsOfData
              AS (
                  SELECT
                        Checksum(NewId()) RandomID
                      , Row_Number() OVER (ORDER BY Checksum(NewId())) RowNum
                      , Count(*) OVER (PARTITION BY ( SELECT NULL )) Countx
                FROM
                    ( VALUES ( 'A'), ( 'B'), ( 'C'), ( 'D'), ( 'E'), ( 'F'), ( 'G'), ( 'H'), ( 'I'), ( 'J'), ( 'K'),
                    ( 'L'), ( 'M'), ( 'N'), ( 'O'), ( 'P'), ( 'Q'), ( 'R'), ( 'S'), ( 'T'), ( 'U'), ( 'V'), ( 'W'),
                    ( 'X'), ( 'Y'), ( 'Z'), ( '0'), ( '1'), ( '2'), ( '3'), ( '4'), ( '5'), ( '6'), ( '7'), ( '8'),
                    ( '9') ) T1 (c1)
                    CROSS JOIN ( VALUES ( 'A'), ( 'B'), ( 'C'), ( 'D'), ( 'E'), ( 'F'), ( 'G'), ( 'H'), ( 'I'), ( 'J'),
                    ( 'K'), ( 'L'), ( 'M'), ( 'N'), ( 'O'), ( 'P'), ( 'Q'), ( 'R'), ( 'S'), ( 'T'), ( 'U'), ( 'V'),
                    ( 'W'), ( 'X'), ( 'Y'), ( 'Z'), ( '0'), ( '1'), ( '2'), ( '3'), ( '4'), ( '5'), ( '6'), ( '7'),
                    ( '8'), ( '9') ) T2 (c2)
                    CROSS JOIN ( VALUES ( 'A'), ( 'B'), ( 'C'), ( 'D'), ( 'E'), ( 'F'), ( 'G'), ( 'H'), ( 'I'), ( 'J'),
                    ( 'K'), ( 'L'), ( 'M'), ( 'N'), ( 'O'), ( 'P'), ( 'Q'), ( 'R'), ( 'S'), ( 'T'), ( 'U'), ( 'V'),
                    ( 'W'), ( 'X'), ( 'Y'), ( 'Z'), ( '0'), ( '1'), ( '2'), ( '3'), ( '4'), ( '5'), ( '6'), ( '7'),
                    ( '8'), ( '9') ) T3 (c3)
                 )
         SELECT
               LotsOfData.RandomID
             , LotsOfData.RowNum
             , LotsOfData.Countx
            FROM
                LotsOfData
            WHERE
                LotsOfData.RowNum BETWEEN 50 AND 59;



  • Jacob Wilkins

    One Orange Chip

    Points: 27856

    The whole pagination + total count thing is a well researched problem, fortunately 🙂

    I can put together a test harness tomorrow to demonstrate, but typically an extra CTE or subquery doing a COUNT(*) turns out to be better than using COUNT(*) OVER () in an all-in-one query form.

    See https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch in the comments and http://www.sqlservercentral.com/articles/paging/70120/, for example.

    As a side note there, there's no need to throw in the dummy PARTITION BY in Joe's code. COUNT(*) OVER () works and is easier on the eyes 🙂

    Ultimately, a lot of this will depend on the exact queries being used, the indexes in place, width of rows, etc.

    With just the right sort of data, I've seen the dueling ROW_NUMBER approach actually come out on top, for example.

    In general, though, it'll be difficult to beat a COUNT(*) subquery/CTE and OFFSET...FETCH. 

    Cheers!

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    Thank you Joe and Jacob!  I will dig deeper based on your responses!
    Lisa

  • jcelko212 32090

    SSCrazy Eights

    Points: 8881

    SoCal_DBD - Monday, June 12, 2017 5:21 PM

    I am returning a subset of search results (based on row_number()) but although I'm returning only, let's say, 10 rows, I need to also return the total count (grand total) that the search criteria would return.  Another team has the same requirement, and they are ultimately calling their stored procedure TWICE.  Once to return the subset of results for populating the grid, and then a second time where they don't limit the number of results that come back so that they can return a total.  This does not seem like an ideal solution to me.  Here is some pseudocode of what I've landed on, but would like to know if anyone has a better suggestion.

    ; WITH LotsOfData AS
    (SELECT RandomID = CHECKSUM(NEWID()),
       RowNum = ROW_NUMBER() OVER (ORDER BY CHECKSUM(NEWID()))
    FROM
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T1(c1)
    CROSS JOIN
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T2(c2)
    CROSS JOIN
      (VALUES
      ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
      ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
      ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z'), ('0'), ('1'), ('2'), ('3'),
      ('4'), ('5'), ('6'), ('7'), ('8'), ('9')    
      ) AS T3(c3)
    )

    SELECT RandomID, RowNum,
       /* This is my attempt, but I know I am running the cte again to get this:*/
       (SELECT MAX(RowNum) FROM LotsOfData) AS 'TotalResults'
    FROM LotsOfData
    WHERE RowNum BETWEEN 50 AND 59

    Thank you!

    Have seen the ROLLUP option in the GROUP BY clause?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply