Paginate rows from SQL

Question

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:

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;

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=).

What is the best way to go about this goal?

score 3 · Answer 1 · edited May 23 '17 at 10:27

3

See the following questions on SQL Server paging queries:

And so on.

edited May 23 '17 at 10:27

Community

1
1

answered Mar 05 '10 at 02:55

Aaronaught

120,909
25
266
342

1

All of those are what I am already doing. They just count the rows of the entire table. I need to be able to go from essentially row 100 to 88 to 12 to 1 or something like that. – Mitchell Skurnik Mar 05 '10 at 03:30
Um, no, they don't. All of the other questions do exactly what you're asking to do. If your question is different from those, then you're going to have to be a lot more specific about *how* it's different. – Aaronaught Mar 05 '10 at 03:33
I need to "SELECT * FROM news WHERE news_cat = 'Pizza' and then paginate that. All those examples just paginate without the where. If I add that to the current SQL statement I end up just getting a few results. – Mitchell Skurnik Mar 05 '10 at 03:45
I see. The 4th and 5th links both address that. Guess it doesn't matter now. Glad you solved it! – Aaronaught Mar 05 '10 at 04:10

score 3 · Accepted Answer · answered Mar 05 '10 at 04:00

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

Aaronaught provided useful links, but hadn't understood yet that you were having trouble numbering the results properly. — Ross Bradbury, Mar 05 '10 at 04:07
Does this affect query performance? is it recommended to do it this way? — Rami Zebian, Oct 01 '20 at 12:03

score 1 · Answer 3 · answered Mar 05 '10 at 04:04

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.

http://www.codeproject.com/KB/aspnet/GridViewNeatPaging.aspx

Paginate rows from SQL

3 Answers3

Linked