I NEED HELP calling a random result from SQL

Results 1 to 2 of 2

Thread: I NEED HELP calling a random result from SQL

  1. #1
    Hael Guest

    Default I NEED HELP calling a random result from SQL

    HI all,<BR>I&#039m sure this is simple but i&#039m not finding documentation on it so I&#039d like to know the most efficient way to pull out a random result (query result) from sql and display it on the fly, each time the page loads. I&#039m doing this for a database of quotes, which I want to load up differently each time the page displays. Thanks in advance for the help. If you want to email or icq me that would be great as well! hael@iname.com/17333832

  2. #2
    Join Date
    Dec 1969

    Default RE: I NEED HELP calling a random result from SQL

    There are two ways to go about this.<BR><BR>1) If the IDs are autonumbers (starting with 1 and going continually to n) and dont expect to change, you can get the count of the items, generate a random number with a time seed, multiply it times 10000, modulus the original count, and add 1. <BR>This will give you a random number between 1 and the number of records in your table. <BR><BR>ex (from the northwind sample db):<BR><BR>DECLARE @counter int, @number int<BR>Select @counter = count(*) from employees<BR>select @number = (convert(int, rand(datepart(ms, getDate())) * 10000) % @counter) + 1<BR><BR>select * from employees where employeeid = @number<BR><BR><BR>If the unique identifier is not a continual int, there are additional steps. First, create a temp table with an identity field and a field of the same datatype as your identifier. Insert your identifiers into the temp table. You now have a table with constant numbering (1 to n), which you can use to select a random ID. Then join the identifier back to your table.<BR><BR>ex. (also from northwind):<BR>set nocount on<BR>create table #temp (intID int identity(1,1), CustomerID nChar(5))<BR>insert into #temp (CustomerID)<BR> Select CustomerID From Customers<BR><BR>DECLARE @counter int, @number int<BR>Select @counter = count(*) from #temp<BR>select @number = (convert(int, rand(datepart(ms, getDate())) * 10000) % @counter) + 1<BR><BR>select * from customers<BR> inner join #temp on<BR> #temp.customerID = customers.customerID<BR>where intID = @number<BR><BR>drop table #temp<BR><BR>Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts