I want to add pagination to a data table so I need to count the numbers of row a filtered query will return. My plan is to show only 5-10 pages from current page in each directions (if the pages exist). However some some queries with that has been filter the result might be fewer pages.
For example if I only show 10 rows per page and plan only to show pagination of 10 pages and then add "..." . Then I should only need to know if the query will return 101 rows, if it has more it could give up and not count more rows to save execution time on DB.
I see something like this:
select count(*) from (
select top 100 id from "your_table"
)
I think this should be a pretty common query so might be better ways of doing it.
I should like to do ask like if select CountLessThen(100) from your_table that return "number of rows" or False.
So it can give up when then row is to many and not count all rows in the table each time.
select count(*) from (
select top 100 id from "your_table"
)
...will never return more than 100.
You'll need to handle the application behavior in the application. As for the SQL, just get the top 101 rows.
select top 101 id from "your_table"
Then if your application detects that it has processed 100 rows, stop processing rows and add "..."