SQL Server 2017 OFFSET + FETCH - How to know last result was reached

I have a table with thousands of entries. I'm building a web application (Razor C#) to present a table with the information.

I have a working pagination system, However I can't find a way to know if the current result set has the last result.

I've thought of doing:

  • pageNumber * resultPerPage < count(all)
  • pre-doing the next page and see if it has any results

I need to do this in order to hide the "next" button. This is my current code (I'm appending this to the query which is built according to the filters set):

OFFSET ((" + page + " - 1) * " + rowsPerPage + ") ROWS
FETCH NEXT " + rowsPerPage + " ROWS ONLY

Thank you!

2 answers

  • answered 2018-03-11 13:59 Sergey L

    Fetch one more row in your request, namely rowsPerPage+1. If the returned result set contains rowsPerPage+1 rows, you'll know there's more records to fetch. If it returns less or equal than rowsPerPage rows, there will be no more pages, so you can hide the next button. The only cost of this approach is that you get one more records each time.

  • answered 2018-03-11 21:04 jarlh

    Skip the OFFSET clause. Keep track of last ID from previous SELECT.

    WHERE ID > last_id_from_previous_select
    FETCH NEXT " + rowsPerPage + " ROWS ONLY