Help using a RowID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help using a RowID

Author

Topic

Balance
Starting Member

24 Posts

Posted - 2008-07-29 : 15:44:35

Hi,

I need to output the value of an image rotating every 8 rows. My plan is to use a RowID with a series of CASE statements to output that image name. I'm having problems with the algorithm behind it, though. I need something like this:

SELECT
ROWID=IDENTITY(int,1,1),
c.contact_id AS CID,
c.fname,
c.lname
INTO CONTACTS2
FROM contacts c

SELECT
ImageName =
CASE
WHEN RowID/RowID = 0 THEN 'hahaha' ELSE 'boo!'
END,
*
FROM CONTACTS2

Obviously this is wrong, as every row will output "boo!". The logic should be "if the RowID corresponds to the first position in that 8-row set, then output the first image. If it's the second RowID (ie: RowID=10) of the 8-row set, then output image 2", etc

Any thoughts? TIA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-29 : 16:02:39

I Did not get that:
rotating every 8 rows
and that:
second RowID (ie: RowID=10) of the 8-row set, then output image 2

Maybe youre looking for modulo in bol?

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Balance
Starting Member

24 Posts

Posted - 2008-07-29 : 16:29:02

webfred,

It looks like this is exactly what I was after!

Here's my code:

SELECT
ImageName =
CASE
WHEN CAST(RowID AS int) % (8) = 0 THEN 'image8.gif'
WHEN CAST(RowID AS int) % (8) = 1 THEN 'image1.gif'
WHEN CAST(RowID AS int) % (8) = 2 THEN 'image2.gif'
WHEN CAST(RowID AS int) % (8) = 3 THEN 'image3.gif'
WHEN CAST(RowID AS int) % (8) = 4 THEN 'image4.gif'
WHEN CAST(RowID AS int) % (8) = 5 THEN 'image5.gif'
WHEN CAST(RowID AS int) % (8) = 6 THEN 'image6.gif'
WHEN CAST(RowID AS int) % (8) = 7 THEN 'image7.gif'
END
FROM CONTACTS2

The output seems alright so far. Does the T-SQL look OK?

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-29 : 17:36:15

I think, RowID is already an INT so you don't need to CAST.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-31 : 11:12:58

Why not to it properly and create a new table to store the id and image names then just join via rowid % 8. Alternatively you can just shuffle the images around a bit and select 'image'+rowid%8+'.gif' (might need a cast to varchar in there).
Either way, the case statement is the cheesiest way to do this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources