Get Total Row Count While Paging

I have a search screen where the user has 5 filters to search on. I constructed a dynamic query, based on these filter values, and page 10 results at a time. This is working fine i

Solution 1:

You don't have to run the query twice.

SELECT ..., total_count =COUNT(*) OVER()
FROM ...
ORDERBY ...
OFFSET120ROWSFETCH NEXT 10ROWSONLY;

Based on the chat, it seems your problem is a little more complex - you are applying DISTINCT to the result in addition to paging. This can make it complex to determine exactly what the COUNT() should look like and where it should go. Here is one way (I just want to demonstrate this rather than try to incorporate the technique into your much more complex query from chat):

USE tempdb;
GO
CREATETABLE dbo.PagingSample(id INT,name SYSNAME);

-- insert 20 rows, 10 x 2 duplicatesINSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;
INSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;

SELECTCOUNT(*) FROM dbo.PagingSample; -- 20SELECTCOUNT(*) FROM (SELECTDISTINCT id, name FROM dbo.PagingSample) AS x; -- 10SELECTDISTINCT id, name FROM dbo.PagingSample; -- 10 rowsSELECTDISTINCT id, name, COUNT(*) OVER() -- 20 (DISTINCT is not computed yet)FROM dbo.PagingSample
 ORDERBY id, name
 OFFSET (0) ROWSFETCH NEXT (5) ROWSONLY; -- 5 rows-- this returns 5 rows but shows the pre- and post-distinct counts:SELECT PostDistinctCount =COUNT(*) OVER() -- 10, 
  PreDistinctCount -- 20, 
  id, name 
FROM 
(
  SELECTDISTINCT id, name, PreDistinctCount =COUNT(*) OVER() 
    FROM dbo.PagingSample
    -- INNER JOIN ...
) AS x
ORDERBY id, name
OFFSET (0) ROWSFETCH NEXT (5) ROWSONLY;

Clean up:

DROPTABLE dbo.PagingSample;
GO

Solution 2:

My solution is similar to "rs. answer"

DECLARE@PageNumberASINT, @RowspPageASINTSET@PageNumber=2SET@RowspPage=5SELECTCOUNT(*) OVER() totalrow_count,*FROM databasename
    where columnname like'%abc%'ORDERBY columnname
    OFFSET ((@PageNumber-1) *@RowspPage) ROWSFETCH NEXT @RowspPageROWSONLY;

The return result will include totalrow_count as the first column name

Solution 3:

Can you try something like this

SELECT TOP 10*FROM 
(
   SELECTCOUNT(*) OVER() TOTALCNT, T.*FROM TABLE1 T
   WHERE col1 ='somefilter'
) v

or

SELECT*FROM 
(
   SELECTCOUNT(*) OVER() TOTALCNT, T.*FROM TABLE1 T
   WHERE col1 ='somefilter'
) v
ORDERBY COL1
OFFSET0ROWSFETCHFIRST10ROWSONLY

Now you have total count in your totalcnt column and you can use this column to show total number of rows

Solution 4:

In my testing with a complex join and ~6,000 records returned, it's much faster to do two separate queries. Faster, as in milliseconds total to get the total and separately bring back a subset of 100 records, vs 17 seconds to do the combined query. Anyone else see this kind of performance hit? Obviously, it could have something to do with the data structure but this is still a huge difference.

Solution 5:

I hope I'm not too late to jump in on this question, but I ran across a very similar problem tonight. I had a paging class that was over inflating the number of results returned because the previous developer was dropping the DISTINCT and just doing a SELECT count(*) of the table joins. While this doesn't solve the 2 query problem I ended up using a nested query so that it looked like this:

Original Query

SELECTDISTINCT
  field1, field2
FROM
  table1 t1
  leftjoin table2 t2 on t2.id = t1.id

Over Inflated Results Query

SELECTcount(*)
FROM
  table1 t1
  leftjoin table2 t2 on t2.id = t1.id

My Results Query Solution

SELECTcount(*)
FROM
  (SELECTDISTINCT
     field1, field2
   FROM
     table1 t1
     leftjoin table2 t2 on t2.id = t1.id) as tbl;