CODEWITHSUNDEEP
sql-serversql-server-2008
facebook-100002276297329
facebook-100002276297329

Reputation: 51

Finding Page Number of specific record

My users table, the table structure is:

ID UserName
 1   AAA
 4   BBB
 7   CCC
 ........
 ........

where ID is a Primary Key and Identity field.

Assume that PageSize is 10, and I want to find the page number for the ID 43. Is there any way to find the ID (43) in which page?

Thanks !

Upvotes: 2

Views: 641

Answers (2)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

DECLARE @PageSize INT = 10
DECLARE @Order DECIMAL(10,2)
DECLARE @UserID INT = 1

SET @Order = (  SELECT  OrderNo
                FROM
                    (SELECT *, ROW_NUMBER() OVER (ORDER BY UserID) AS OrderNo
                     FROM   Users
                    ) AS U
                WHERE U.UserID = @UserID
             )

SELECT @UserID AS UserID, CEILING(@Order/@PageSize) PageNo

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5798

You can use Row_Number with partition option you can achieve this.

SELECT  ROW_NUMBER() OVER (ORDER BY UserID) AS OrderNo, *
                     FROM   Users

Now you simple give where condition with page size to find out the page no. In your case, simple query give the result without partition option.

Declare @userid int =  43,    @PageSize int = 10

;with cte as
(
  SELECT  
       ROW_NUMBER() OVER (ORDER BY UserID) AS OrderNo, *
  FROM   Users 
)                     

Select 
      CEILING(OrderNo/@PageSize) PageNo 
FROM 
       cte where userID = @UserID

Row_number with Partition option. If you want to re-order on another column then ,

;with cte as
    (
      SELECT  
           ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY UserID ) AS OrderNo, *
      FROM   Users 
    )           
Select 
          CEILING(OrderNo/@PageSize) PageNo 
    FROM 
           cte where userID = @UserID

Upvotes: 0

Related Questions