[thelist] Random value between 1 and x with SQL Server 2K

Guy Babbitt Guy_Babbitt at FSAFOOD.COM
Sun Jul 29 10:26:29 CDT 2001


This should be simple, but for some reason I am having some problems with my
logic. I am creating a function that will select records randomly from a SQL
database. I have isolated and finished all of the logic necessary to actually
pull the record once I have a value for it (which wasn't nearly as easy as I had
expected it would be), but I am stuck on a way to randomly select a value
between 1 and x (where x is the number of rows returned from an initial query).

So, here is what I have so far. This script is completely server side as I only
want to pass parameters from the web page, I do not want to work the logic from
the web page.

declare @nvarrc int
declare @nvarrs int
set @nvarrc = (select count(*) from products where categoryid=7)
set @nvarrs = (ceiling(rand()*(@nvarrc)))

So, can someone please help me figure out the correct way to execute the last
line of code there to actually come up with a random value between 1 and
@nvarrc?  The cieling function combined with rand() is just not truly random.

Thanks for your feedback!

Guy Babbitt
Systems Architect
P 206.933.4976
C 253.691.2333






More information about the thelist mailing list