Getting Top (starting no , Ending no) rows from sqlserver

Question

I'm using Grid view in my asp.net page and I'm fetching data from SQL Server and placing in my grid view.

Now my problem is, I'm using paging and placing 50 rows per page in Grid view. I would like to fetch top 50 rows from database in the start up and bind to the Grid View and when i click on next page, then again go to the database and fetch Second Top 50 rows and bind to GV. and this process hasto continue until last row is fetched from Database.

Can you tell me How to write Query in achieving this??

Answer 1

Typically you will have two parameters passed to this query, @pageNum and @pageSize, then you can do this:

With ranked AS   --- Or you can make it a view
(
   SELECT ROW_NUMBER() OVER(ORDER BY OrderField) AS RowNum,  * 
   FROM YourTableReference
)
SELECT *   --Your fields here
FROM Ranked
WHERE RowNum BETWEEN ((@PageNum - 1) * @PageSize + 1)
                 AND (@PageNum * @PageSize)
ORDER BY SomeField

Live Demo