CODEWITHSUNDEEP
sqlsql-server
Slee
Slee

Reputation: 28248

calculate number of pages of records in select statement

SO I am trying to figure out how many pages I will need to display my records, basically want a page count.

I was going to do something like this:

SELECT COUNT(*) % 50 
FROM Customers 
WHERE ManufacturerID=@ManufacturerID 
AND ModifiedOn > @tStamp

SO for that statement I would want to get back a 2 for 99 records, 2 for 100, and 3 for 101

Will this work? Is this a bad practice? Seems a little wonky on me testing some values.

EDIT FOR CLARIFICATION: I don't want to get the paged records, just the total number of pages

Upvotes: 4

Views: 18217

Answers (3)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107536

To calculate the number of pages, just take the ceiling of the number of records over the page size:

SELECT CEILING(COUNT(*) / @PageSize) FROM ...

with @PageSize being 50 in your case. Or, since your application probably knows the desired number of records to show per page, just write a query that returns the COUNT(*) from your table, and do the calculation in your code. For example:

var totalPages = Math.ceil(recordCount / pageSize);

When you get to the point where you want to select the records that belong to a page, I do it like this (just in case you were curious):

WITH Paged AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY SortColumn) AS [RowNum]
        ,* -- because we're going to choose which columns we need later
    FROM
        [MyTable]
)
SELECT 
     [Column1]
    ,[Column2]
    ,etc...
FROM 
    Paged
WHERE 
    [RowNum] BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY 
    [SortColumn]

You have to pass in @PageNumber and @PageSize from your application.

Upvotes: 17

Kris
Kris

Reputation: 565

I think, the basic question of getting total number of pages still remained un-answered.

By using a simple logic, I think it can be achieved in SQL Query:

TotalPages = (Count(*) OVER() + @NumberOfItems - 1)/@NumberOfItems

where "@NumberOfItems" is number of items to be displayed in a Page. Hope this helps someone.

Upvotes: 3

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

If you use 

ROW_NUMBER() OVER ( {ORDER statement} ) AS ROWNUMBER

in your paged SELECT statement, you can then add 

COUNT(*) OVER() AS TOTALCOUNT

to get the total number of records found by the query

Upvotes: 4

Related Questions