Paginate rows from SQL
I am trying to finish writting my news/blog ASP.net C# application and I am getting stuck on how to return only 10 items at a time after filtering items based on tag or keyword.
This is an example of what I am looking for:
- User queries sql by browsing to ?tag=Pizza
- Backend finds all tags that equal the query string (sent over as a @ parameter). This part I have figured out.
Now I want it to just return 10 rows at a time from this filtered statement. I was easily able to do this with this when I wasn’t filtering anything out:
- SQL Server Conditional Order By
- SQL Server Transaction inside stored procedure
- How to solve - Invalid length parameter passed to the LEFT or SUBSTRING function
- java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found
- How to get the physical_name of the secondary log shipping databases?
- Linq Performance with predicates
I then display at the bottom of the page the user the option to view more. (This part is easy as I just need to add 10 to a query string of ?num=).
sqlCommand.CommandText = "SELECT * FROM (SELECT row_number() over(ORDER BY news_id) AS row_number, * FROM news) T WHERE row_number BETWEEN (SELECT rowcnt FROM sys.sysindexes WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start+10) AND (SELECT rowcnt FROM sys.sysindexes WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start) ORDER BY news_id DESC"; sqlCommand.Parameters.Add("@start", SqlDbType.Int).Value = start;
What is the best way to go about this goal?
3 Solutions collect form web for “Paginate rows from SQL”
Understanding how row_number numbers the results is a little tricky at first.
It looks like two things are making this more complicated than it could be. The row_number function must be in the same SELECT or an outer SELECT than the filtering is happening, otherwise you are numbering the whole table, rather than numbering the results. Your query numbered all the results in the table before the rows were limited because row_number was used in a SELECT that was inside the SELECT with the WHERE.
Second, it looks like you are hitting sys.indexes to get the number of rows (which I am not sure is always accurate) to the the row number range in reverse when you can just add a DESC to the row_number function.
SELECT * FROM ( SELECT row_number() over(ORDER BY news_id DESC) AS row_number, * FROM news WHERE --user filter goes here news_cat = 'Pizza' ) AS T WHERE row_number BETWEEN @start AND @start + 10
See the following questions on SQL Server paging queries:
- Paging SQL Server 2005 results
- How to do pagination in SQL Server 2008
- How to return a page of results from SQL?
- Pagination in SQL Server
- What is the best way to paginate results in MS SQLServer
- How can I do server side pagination of results in SQL Server 2000?
And so on.
I wrote this article on codeproject 2-3 years ago when paging support is still fairly limited in the framework, perhaps still a good reference.
If you only concern about the data, just check out the section on how I retrieve paged data.