Random record retrieving!

Question

Random record retrieving!

apinnelli

Mr or Mrs. 500

Points: 525
More actions
June 7, 2002 at 4:34 am

#78773

Hi,
i need to retrieve one record in random mode from a table which contains products. This table has an identity column. Is there a way to do this with one sql statement?
Antonio

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Unfortunately no, there are a few examples of generating a truer random number then you get by default with RAND() but most use temp tables and multiple lines of sql to generate a random number. However, if you are using SQL 2000 you could take one of these examples and make into a function to give a random number or you could build a randomizer as an extended stored procedure in C++.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

vitovex Grasshopper Points: 19 More actions · Answer 2

Hi,

this topic is very interesting. Infact I have a similar problem, but I wrote a lot of Visual Basic code to retrieve a random record from a table.

I hope that some expert on this forum can give us a solution.

I submitted this problem to many SQL gurus, but anyone helped me.

Best regards,

Vito

DROP TABLE ORACLE_DEVELOPERS

apinnelli Mr or Mrs. 500 Points: 525 More actions · Answer 3

HI,

I have found the solution!!!!!

SELECT TOP 1 * FROM PRODUCTS WHERE ID_REC > (SELECT MAX(ID_REC) * RAND() AS ID_REC FROM PRODUCTS) ORDER BY ID_REC

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 4

Antonio,

Unfortunately this will not give an even distribution of your records, unless you do not have any gaps in the numbering sequence.

If your product numbers were 1,2,3,10 and 100.

Then 100 will come out about 90 times more often than 1,2 or 3.

But if you can think of a way to overcome this, then please let me know. I need a better solution as well.

Kind regards

Henrik Staun Poulsen

Stovi Software, Denmark

mdburr Default port Points: 1483 More actions · Answer 5

Try this (using apinelli's example table):



SELECT *

  FROM Products AS a

  JOIN (SELECT b.ID_REC

          FROM Products AS b

          JOIN Products AS c

            ON b.ID_REC >= c.ID_REC

         GROUP BY b.ID_REC

        HAVING COUNT(*) = CEILING(RAND() * (SELECT COUNT(*) FROM Products))) AS d (ID_REC)

     ON a.ID_REC = d.ID_REC

The subquery does the job of randomly selecting a product id from the table. The outer query gets the full row of data for the randomly selected product. This query will only work properly if all of the ID_RECs are unique, which - given apinnelli's assertion that the table has an identity column - can be assumed.

Matthew Burr

Nazim Say Hey Kid Points: 664 More actions · Answer 6

you can check this Article http://www.sqlteam.com/item.asp?ItemID=8747 .

this will work great if you are using Sql 2k.