CODEWITHSUNDEEP
c#asp.netsqlsql-serversql-server-2008
Krishna Thota
Krishna Thota

Reputation: 7036

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

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??

Upvotes: 3

Views: 242

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

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

Upvotes: 5

Niladri Biswas
Niladri Biswas

Reputation: 4171

Try Paging GridView with ROW_NUMBER() and you will get the idea as how to do it.

Also SQL Server - Server-side paging with Row_number() function will help.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

You can create a sproc 

sp_fetcrangeofrecords
@st int,
@ed int

As
BEGIN
WITH CTE as (select row_number() over (order by (select 0)) as rn,* from your table)

select col1,col2,... from table where rn between @st and @ed
END

Upvotes: 2

opewix
opewix

Reputation: 5083

Read more about SQL paging, there are several approaches http://www.codeguru.com/csharp/.net/net_data/article.php/c19611/Paging-in-SQL-Server-2005.htm

Upvotes: 1

Related Questions