As part of an API I have a query that loads recent orders. The user will specify @numOfResults they want and @nextResult = -1 to get the top @numOfResults newest orders.
Declare @numOfResults int = 10
Declare @nextResult int = -1
select TOP (@numOfResults + 1) OrderID, OrderDate
from orders
where (@nextResult <= 0 or @nextResult is null or OrderID < @nextResult)
ORDER BY OrderID DESC
On subsequent calls they will pass in the returned @nextResult to get the next set of @numOfResults. Works fine.
The reason it queries 1 additional is to see if we are at the end of the list (in the real query there are other constraints which do not matter here). If we have exhausted the list then nextResult is returned as -1 (not shown here) to indicate the end of the list, otherwise we return the second to last OrderID as nextResult. In this way the user can loop until they have retrieved all the orders.
Keep in mind this is part of an existing API and cannot be changed.
So this all works very well, but now we have introduced 2 different sequences of OrderIDs, lets say 2000-2999 and 8000-8999.
So to return the newest orders the query is modified to sort by OrderDate instead of OrderID:
Select TOP (@numOfResults + 1) OrderID, OrderDate
from orders
where (@nextResult <= 0 or @nextResult is null or OrderID < @nextResult)
ORDER BY OrderDate DESC
Of course this breaks the @nextResult logic. The first call with @nextResult = -1 will get the top newest @numOfResults regardless of OrderID (correct), and the @nextResult to retrieve (also correct), but on the next call we are limiting the result to < @nextResult.
It should work as follows: given the table:
2050 2020-10-22
2049 2020-10-22
2048 2020-10-20
8050 2020-10-20
8049 2020-10-18
8048 2020-10-18
8047 2020-10-17
2047 2020-10-15
2046 2020-10-14
A call with @numOfResults = 5, @nextResult -1 will yield:
2050 2020-10-22
2049 2020-10-22
2048 2020-10-20
8050 2020-10-20
8049 2020-10-18
where nextResult = 8049
then the next call @numOfResults = 5, @nextResult 8049 should yield:
8048 2020-10-18
8047 2020-10-17
2047 2020-10-15
2046 2020-10-14
where nextResult = -1
At first I thought this would be an easy fix until I realized it was not. The query needs now to 'skip' the results prior to nextResult (an unknown number of results). I'm sure I could come up with some abomination that does it but I'd like to be a eloquent as possible.
Any ideas?
Thanks!