How can I pick a random record from a table, BUT I

Results 1 to 2 of 2

Thread: How can I pick a random record from a table, BUT I

  1. #1
    Join Date
    Dec 1969

    Default How can I pick a random record from a table, BUT I

    Hi,<BR>I read a very interesting issue in this forum which is "How can I pick a random record from a table, using a stored procedure?"<BR>But my problem is more or less is same...What I need is the result from the next random search should not to be the same as the already outputed result or recordset.

  2. #2
    Join Date
    Dec 1969

    Default RE: How can I pick a random record from a table, B

    Pretty easy, I don&#039;t know which method you used (I wrote a FAQ and there&#039;s another article) Personnaly I would say my suggestion is better, but that&#039;s just me :-)<BR><BR>You have to add the select RecordID to a new table. The following is an adjustment to my solution:<BR><BR>CREATE PROCEDURE spGetRandomRecord<BR><BR>AS<BR><BR>--Declare local variables<BR>DECLARE @counter int, @randno int, @uBound int, @lBound int<BR><BR>--You don’t want to get the results of the interim <BR>--SELECT statements passed to the recordset <BR>--so you have to set NOCOUNT on SET NOCOUNT ON<BR><BR>--Set the upper and lowerbound for the random number<BR>SELECT @uBound = Max(ID) FROM table<BR>SELECT @lBound = Min(ID) FROM table<BR><BR>--Get a random number<BR>SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0)<BR>SET @Counter = 0<BR><BR>--Keep trying until we find a record. This is necessary <BR>--if there are gaps in the ID field.<BR>WHILE @counter = 0<BR> BEGIN<BR> --Check if there is an record with the specified ID<BR> IF EXISTS(SELECT ID FROM table WHERE id = @randno) AND NOT EXISTS(SELECT ID FROM UsedID WHERE ID = @randno)<BR> BEGIN<BR> --The record exists, so get the complete record<BR> SELECT * FROM Table WHERE id = @randno<BR> --Set the @counter variable to 1 to leave the WHILE loop<BR> SET @counter = 1<BR> ************* NEW PART<BR> --Insert the ID in another table to prevent the same record<BR> --being selected.<BR> INSERT INTO UsedID (ID,TheDate) VALUES (@randno,GetDate())<BR> ************* END NEW PART<BR> END <BR> ELSE<BR> BEGIN<BR> --The SELECT didn&#039;t return any records, so get a <BR> --new random number and try again<BR> SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0) <BR> END<BR> END<BR><BR>I also included the current date/time in the UsedID table. So you could add something that would allow the used id after a week or so..

Posting Permissions

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