Return total records from SQL Server when using ROW_NUMBER

Question

I would like to return the total number of records in the database so I can set up pagination. How do I return the total number of records in the DB when using the following paging method in SQL Server 2008?

    ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized]
    (
        @PageSize int = 20,
        @PageNumber int = 1,
        @WarehouseCombinationID int = 1,
        @CategoryId int = 58,
        @OrderBy int = 0,
        @TotalRecords int = null OUTPUT
    )
    AS
    BEGIN
    WITH Paging AS (
        SELECT rn = (ROW_NUMBER() OVER (
        ORDER BY 
            CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
            THEN pcm.DisplayOrder END ASC,
            CASE WHEN @OrderBy = 0
            THEN p.[Name] END ASC,
            CASE WHEN @OrderBy = 5
            THEN p.[Name] END ASC,
            CASE WHEN @OrderBy = 10
            THEN wpv.Price END ASC,
            CASE WHEN @OrderBy = 15
            THEN wpv.Price END DESC,
            CASE WHEN @OrderBy = 20
            THEN wpv.Price END DESC,
            CASE WHEN @OrderBy = 25
            THEN wpv.UnitPrice END ASC  
        )), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p
        INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
        INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
        INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
        WHERE pcm.CategoryID = @CategoryId AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0)
        AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)    
    )
    SELECT TOP (@PageSize) * FROM Paging PG
    WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize 

    SET @TotalRecords = @@ROWCOUNT 

    END

See Efficient way of getting @@rowcount from a query using row_number — Martin Smith
– Martin Smith, Commented Apr 26, 2011 at 13:22
@Martin thats what I was looking at but it doesn't say how to return the total number of rows? — izip
– izip, Commented Apr 26, 2011 at 13:24
I edited my comment and changed the link. Which link are you referring to? The SQLServerCentral one or the StackOverflow one? BTW It is part 2 of the SQL Server Central article that covers this. — Martin Smith
– Martin Smith, Commented Apr 26, 2011 at 13:35
@Martin, Thanks the edit makes sense. That works, it adds a TotalRows column to my record set. How can I assign the TotalRows as an output parameter? — izip
– izip, Commented Apr 26, 2011 at 13:37

marc_s · Accepted Answer · 2011-04-26 13:37:12Z

60

I typically do it this way - never really checked whether it's very efficient from a performance point of view:

WITH YourCTE AS 
(
   SELECT 
       (list of columns),
       ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum' 
   FROM dbo.YourBaseTable
)
SELECT 
    *,
    (SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows' 
FROM 
    YourCTE
WHERE   
    RowNum BETWEEN 101 AND 150

Basically, the RowNum value will have values 1 through the total of rows (if you don't have a PARTITION BY in your CTE) and thus selecting MAX(RowNum), you get the total number of rows.

answered Apr 26, 2011 at 13:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

izip Over a year ago

Thanks. How do you assign the TotalRows to an output parameter from a store proc? It works both ways, yours and Martins, but would like to return it as a param rather than a new column in my dataset. EDIT, got it: @TotalRecords = PG.TotalRows

Bob Banks Over a year ago

Very clever! Works great (and fast) for me.

sohtimsso1970 Over a year ago

You should replace the Max(RowNum) field with Count(*). Count performs a lot faster, at least in my queries.

Billa Over a year ago

How do i assign TotalRows to the @TotalRecords int = null OUTPUT ?

Billa Over a year ago

Invalid object name 'MyCTE'. I tried this

SELECT @TotalRecords = (SELECT MAX(RowNumber) FROM MyCTE)       SELECT Id, Name  FROM MyCTE WHERE RowNumber BETWEEN Start AND End

. I think CTE will be available for only one immediate SELECT statement

amantur · Accepted Answer · 2013-04-28 23:21:34Z

28

Well I had used #temp tables in past but found out another solution using Count(..) OVER() to find total number of rows few days ago. It works something like this:

SELECT COUNT(id) OVER() [Total], ROW_NUMBER() OVER(ORDER BY id DESC) [RowNo]
, [other columns]
FROM Table

The reference page is here.

answered Apr 28, 2013 at 23:21

amantur

40.6k22 gold badges136 silver badges192 bronze badges

3 Comments

bcleary Over a year ago

This is the best solution I have found for this problem thus far. Has the benefit of not significantly impacting query execution time.

Fedor Over a year ago

The problem here is that RowNo cannot be used inside WHERE clause.

Raybarg Over a year ago

I know this is old but this solution is much better than the one presented above. Fedor pointed out that RowNo cannot be used inside WHERE clause, but it can be used if this query is enclosed: SELECT * FROM (TheVillageIdiotsQuery) AS SubQuery WHERE RowNo BETWEEN 1 AND 2

Collectives™ on Stack Overflow

Return total records from SQL Server when using ROW_NUMBER

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related