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

[Warden, Matt]

On Jul 29, Guy Babbitt had something to say about [thelist] Random value...

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

set @nvarrs = round( (rand()*@nvarrc) ,0)

I couldn't find how round() works so I couldn't verify that it would keep
this truly random. But, it's closer than ceiling().

Not sure how this value is going to help you unless you ensure your
ordering is the same and do something like:

select foo
from bar
where foo=bar
having count(*) = @nvarrs

but I don't think I've ever seen a having clause with an equality. doesn't
mean you can't (maybe it does), but it does give me that weird feeling
like rudy's going to yell at me or something.

;-)


hth,


--
mattwarden
mattwarden.com