asked
Query for Next row based on an Index
Hi, Complicated query to do and ask/explain, at least for me, so here goes... Table= 'Page ' with an index named 'LibSort' build on field 'library_sort'. 1. I query the page table for a row (any row at this point) and what I want returned is the next row that would be next in-line if the table was ordered in LibSort order and only return a single row. If the original query returned Deed book 200 page 54, then the query for the app's next button needs to return 'Deed book 200 page 55 if it exists, otherwise it needs to return the next higher number as shown in the LibSort index which could happen if it is at the end of book 200 and the next page is actually book 205 page 18. The point here is I need it to return the next page based on the LibSort index. I do not know what any of the keys would be, other than I've got an index based on the order that represents flipping through a book page by page. Hope that explains, Thanks, Stanley
It's difficult to say without seeing some table definitions, data, and expected results, but I suspect that the T-SQL `LEAD` function may be of use...
https://msdn.microsoft.com/en-us/library/hh213125.aspx
If you need hints on how to post data for queries, read this article:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Hi Thomas, The lead function looked promising until I saw it was for getting at the NEXT row in the returned set. It also mentioned there could be no -1 or negative numbers. So how would it work in getting the PREVIOUS row going in the other direction? It also mentions that it is applied to the currently returned result set, so I really have no way of including the unknown row as part of the original select statement. All I know is I need the next or previous row based on the LibSort index. Thanks, Stanley
18 People are following this question.
