Help returning previous row number ID

I am trying to create a tag based image viewer for my site.  When a user clicks a photo from the thumbnail page it will pass the image ID and tag that thumbnail belonged to.  I would like to pass that imageID and tagname to a stored procedure and return the previous and next imageID's.  If the image ID hits a hard limit, (ie: the passed ID was the first or last in the query), it would return zero.

I am pretty familiar with SQL and played around with the row_number function a bit but not really getting far.  

swabeuiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

BinuthCommented:
can you provide table structure ?
0
swabeuiAuthor Commented:
Sure, here they are:
tbl_images
----------
int ImageID
varchar imagepath
varchar imagename  
datetime postdate
 
tbl_taglink
-----------
int taglinkID    (links to imageID)
int tagID        (links to tagID in table below)
 
tbl_tags
--------
int tagID
varchar tagname

Open in new window

0
BinuthCommented:
try this sample
declare @Tagname AS varchar(1000)
declare @ImageID AS INT
 
set @Tagname = ''
set @ImageID = 1
 
select 
	row_number() over(order by ImageID asc) AS RowNum,
	ImageID,
	tagname
into #tmp
from tbl_images
inner join tbl_taglink on tbl_images.ImageID = tbl_taglink.taglinkID
inner join tbl_tags on tbl_taglink.tagID = tbl_tags.tagID
 
 
declare @intCurrentRowNum as int
select @intCurrentRowNum = Rownum from #tmp where Tagname = @Tagname and ImageID = @ImageID
 
select 'Prev.' ,@intCurrentRowNum-1
union 
select 'Current',@intCurrentRowNum
union
select 'Next',case when (select max(rownum) from #Tmp) = @intCurrentRowNum then 0 else @intCurrentRowNum + 1 end

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

swabeuiAuthor Commented:
Very cool.  It just needs one tweak, it need to return the actual imageID of the previous and next image instead of the row number.

Thanks!
0
swabeuiAuthor Commented:
I'm not sure if you have seen my comment above yet, but if so could you post an updated script with the tweak mentioned above?
0
BinuthCommented:
sorry for late replay(i was on leave last day)

try now...
declare @Tagname AS varchar(1000)
declare @ImageID AS INT
 
set @Tagname = ''
set @ImageID = 1
 
select 
        row_number() over(order by ImageID asc) AS RowNum,
        ImageID,
        tagname
into #tmp
from tbl_images
inner join tbl_taglink on tbl_images.ImageID = tbl_taglink.taglinkID
inner join tbl_tags on tbl_taglink.tagID = tbl_tags.tagID
 
 
declare @intCurrentRowNum as int
select @intCurrentRowNum = Rownum from #tmp where Tagname = @Tagname and ImageID = @ImageID
 
select 'Prev.',ImageID from #tmp Where RowNum = @intCurrentRowNum-1
union
select 'Current',ImageID from #tmp Where RowNum = @intCurrentRowNum
union
select 'Next',ISNULL(ImageID,0) from #tmp Where RowNum = @intCurrentRowNum+1

Open in new window

0
swabeuiAuthor Commented:
Thanks for the update.  I'm triple checking the code to see if i got something wrong but it seems to return the next and previous imageID in the database regardless of what the tag is set to.
0
BinuthCommented:
hmm .. that's my mistake tagname should come with first query

try now...
declare @Tagname AS varchar(1000)
declare @ImageID AS INT
 
set @Tagname = ''
set @ImageID = 1
 
select 
        row_number() over(order by ImageID asc) AS RowNum,
        ImageID,
        tagname
into #tmp
from tbl_images
inner join tbl_taglink on tbl_images.ImageID = tbl_taglink.taglinkID
inner join tbl_tags on tbl_taglink.tagID = tbl_tags.tagID
WHERE Tagname = @Tagname
 
 
declare @intCurrentRowNum as int
select @intCurrentRowNum = Rownum from #tmp where ImageID = @ImageID
 
select 'Prev.',ImageID from #tmp Where RowNum = @intCurrentRowNum-1
union
select 'Current',ImageID from #tmp Where RowNum = @intCurrentRowNum
union
select 'Next',ISNULL(ImageID,0) from #tmp Where RowNum = @intCurrentRowNum+1

Open in new window

0

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
swabeuiAuthor Commented:
Thanks! It seems to work well now.  If it is an easy thing to fix, it doesn't seem to return 'zero' when there are no more records left, they are just missing from the list.  Not a biggie, I can check for missing elements in the code.

Thanks for the help, this will make things a lot easier.

John
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.