Get total count before getting a subset from numbered results
-
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 59Thank you!
-
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;
-
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!
-
Thank you Joe and Jacob! I will dig deeper based on your responses!
Lisa - SoCal_DBD - Monday, June 12, 2017 5:21 PM
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply