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):
ORDER BY ID OFFSET ((" + page + " - 1) * " + rowsPerPage + ") ROWS FETCH NEXT " + rowsPerPage + " ROWS ONLY
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.
OFFSETclause. Keep track of last ID from previous
WHERE ID > last_id_from_previous_select ORDER BY ID FETCH NEXT " + rowsPerPage + " ROWS ONLY