I don't have the SQL for this but the logic would be: INT((RAND()*100)) which should give you a random number from 0-100.
--JW "Mark A. Kruger - CFG" wrote: > Yes - I think I'm coming to the same conclusion - plus, your idea would > return an INT, but it would NOT return an int within a specified range of > ints (1 to 123 for example). > > -----Original Message----- > From: Haggerty, Michael A. [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 10:30 AM > To: CF-Talk > Subject: RE: Random row from SQL > > Well, you could use the RAND() function to get a random number, > multiply it > by GETDATE(), divide by 100000 and use INT() to return a whole > number... but > that would be a lot of SQL. > > You are better off creating the variable in CF and passing it into the > SP. > Random numbers in SQL Server aren't really as random as we would like; > once > you reset the server the random number counter resets and you get a > repeating pattern. > > Mike > > -----Original Message----- > From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 2:44 PM > To: CF-Talk > Subject: RE: Random row from SQL > > Yes, > > I know how to do this in CF - I was wondering if anyone had figured out > a > way to do it within a Stored proc? > > something like.... > > OPEN CURSOR MyAds > > FETCH NEXT ABSOLUTE Randomize(1, @maxCursorrows) INTO @ChosenRow > > .............. > > I've tried using the "RAND" Function, but it is does not produce a > randome > number within a given range - it only does 0 to 1 using a seed value. > > Mark > > -----Original Message----- > From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 9:37 AM > To: CF-Talk > Subject: RE: Random row from SQL > > >From memory, so the syntax might be a touch off... > > <cfset temp=Randomize(Seconds(Now()) * Minutes(Now()))> > <cfset ShowRow = RandRange(1, qGetAds.Recordcount)> > <cfoutput query="qGetAds" StartRow=ShowRow MaxRows=1> > ... > </cfoutput> > > C. Hatton Humphrey > > > -----Original Message----- > > From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, February 13, 2002 2:31 PM > > To: CF-Talk > > Subject: Random row from SQL > > > > > > A while back someone posted some code to generate a random number > between > > two numbers using SQL. I'd like to be able to do that. I want to be > able > > to return a random row from among rows in an ad database. Does > > anyone have > > ideas on how this might be accomplished? > > > > Mark > > > > ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
