SQLTeam.com | Weblogs | Forums

Getting next set of records ordered by date but based on id

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!

This sounds like some sort of paging that they want to implement. You can use Offset and let them pass in the Number of Results and the page they want. They can keep track of it on the other side

Declare @numOfResults int = 10
Declare @nextResult int = 1

select  OrderID, OrderDate  
from Orders
order by OrderID desc
offset (@nextResult * @numOfResults) -1 ROWS
fetch next @numOfResults rows only
1 Like

That would work and I've done that before, problem is they don't want to change the interface. However the solution will probably look something like that...if I could figure out what position the nextResult order is then I could feed that into the paging query. I just want to return all orders after (well before) the order they send in, so how can I find the ordinal position of the nextResult order id they pass in in that query...seems like I used to know that (something like row?)