[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

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.  

0
swabeui
Asked:
swabeui
  • 5
  • 4
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now