Click here to Skip to main content
16,015,583 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
req:

i have about 180000 records i want to fetch desired 20 records at a time which i am going to use in the data grid.

right now i am using Row_Number() and assigning Row_Num for each row and storing them in a temp table than i can get the desired rows based on Row_Num, but it takes long time for the very first time to assign Row_Num to the 180000. Please suggest me if any efficient way to do this.
Posted

1 solution

You don't need to assign a row number

SQL
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)
SELECT *
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;


http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx[^]
 
Share this answer
 
Comments
santoshamrutha 8-Sep-10 10:28am    
Hi mark you are right but when my number of rows are 180000 to load the last page it's taking lot of time as it has to process the entire 180000 records to get the last 20 records, is there any possible idea to get only the required records without executing the entire 180000 records.
[no name] 8-Sep-10 10:55am    
Create a view that adds the row number, such as in the CTE above, and execute the query against it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900