Next/Previous Item

I am having trouble with getting the prev/next item in my list.  I have two pages, one is a list page which lists out the items, the other is a view page which shows more details, etc.

The list page gets it data from the following sql (simplified version)

select ItemNumber, ISNULL(ListOrder, 9999) from itemList order by ListOrder, ItemNumber

Users have the ability to rearrange the order items are listed which is stored in the ListOrder column.  If an item has not been ordered, or just added, its ListOrder is NULL which is set by the query to 9999 so it falls to the bottom, and then I order by ItemNumber as well to maintain a consistent display order for items with the 9999 ListOrder.

On the view page, I need a First, Prev, Next, Last button that will cycle through the list in the correct order.  The First and Last are easy; just used a select top 1 on the list for first, and a select top 1 on the list ordered DESC for last.  The problem comes with Prev/Next - It would be simple if all items had a ListOrder; its simply a select top 1 with a where clause ListOrder<Current or ListOrder>Current respectively.  However, not all items have a ListOrder value, which is where the problem lies.  I can't do a ListOrder < Current anymore b/c if the ListOrder is set at 9999 it could be the first 9999, middle or last.. and ListOrder < Current will automatically use it as the first.  I'm lost as to how to approach this.  And I can't just go through and assure all items have a ListOrder, its not an option at this point.

An example of some data:
ItemNumber      ListOrder
104                    1
103                    2
106                    3
101                    9999
105                    9999

The problem area: Current is one of the 9999 and need Prev
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Could you change the code so the list order is based on "9999" + itemnumber?  If so, then you would have

An example of some data:
ItemNumber      ListOrder
104                    1
103                    2
106                    3
101                    9999101
105                    9999105

so the last ones will be ordered in the order they came in falling at the tail end of the list.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TrinigAuthor Commented:
Wow.. I feel really stupid - I think that should work.  I'm going to implement it now and test it.
TrinigAuthor Commented:
Nice and simple, Works great.  I was really over complicating things trying to come up with a solution.  Thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.