SQL Randomize Code

Select random records

[abulhallaj]

How can I select random records with SQL commands on my ASP project?

[aspdaddy]

Something im workin on right now .. :)

HTH

Function RandomNumber(intHighestNumber )
Randomize
RandomNumber = Int(Rnd * intHighestNumber)+1
End Function

strProductList=""

' get all id's
set objRS=objConn.Execute ("SELECT ID FROM PRODUCTS")

myArr = objRS.GetRows()
for i=1 to 3
strProductList = strProductList & cstr( myArr(0,RandomNumber( ubound(myArr,2)) )) & ", "
next
strProductList=left(strProductList,len(strProductList)-1)

' get the 3 random ads
strSQL = "SELECT * FROM Products WHERE Products.ID IN ("& strProductList &")"

[bcc1234]

I'm pretty sure that every db has a function that generates a random double, so you can try something like

select * from mytable order by random() limit 10;

replace random() and 10 with the name of the function and the number of records you need.

But it would take a lot of time to execute on really large tables.

[killroy]

In MySQL:

SELECT * FROM table ORDER BY RAND();

SN

[aspdaddy]

>I'm pretty sure that every db has a function >that generates a random double

Maybe, but not all db functions are available through ASP/ADO

[bcc1234]

Maybe, but not all db functions are available through ASP/ADO

Hmm, you can't pass an arbitrary query to the db?

[aspdaddy]

Depends on the provider being used -
Jet.4.0 used to have a bug seeding the rnd() function, msde has probably fixed it but I still use the asp way as I know it works with any db/provider :)

[abulhallaj]

I think the aspdaddy say true; some SQL functions can't run on ASP code and return error.

let come back to my problem : I use Access database with Microsoft JET 4.0 provider. it have any function?

[f00sion]

select field1, field2, newID() as expr1 from sometable order by expr1

the newid function generates a guid for each row returned and it will be different each time the query is executed.

[mattur]

In MS SQL7+ on W2K

SELECT TOP 1 someColumn
FROM someTable
ORDER BY NEWID()

...from aspfaq.com

<added>
Oops, just noticed you're using Access, see:
h*ttp://www.aspfaq.com/show.asp?id=2132
</added>

[abulhallaj]

Thanks to all for your help!