I working on a new site that requires me to show a page with a single product and it's information. I have a Next and previous button that will take the user to the next item in that category / subcategory.
basic Querey to show all items in category:
select * from db where category = xx and type = xa
When I view one item from this category all I do is request the ID assigned to that item.
Select * from db where id = 12345
*** My issue ***
Because the ID# are randum and will change when the customer adds and deletes items I do not want to use anything like " where ID > @id "
I want to use Row_Number Over() to find where in the query my current item is so that I can turn around and request the next item.
I've been hacking around but my query keeps coming back with row = 1.
SELECT ROW_NUMBER() OVER (ORDER BY STYLENO) AS 'MYNEXT2' FROM (SELECT id, row_number() OVER (ORDER BY styleno) as 'mynext', category, designerno,styleno FROM mydb
WHERE category = 100 AND type = 10 ) AS XR34 WHERE id = 10363
Please help me save what little hair (and brain cells) I have left.