SQL Randomize Code

need one line sql code link RND

[abulhallaj]

Hi to all

I need to select a random record off data with one line query. it's need to be used inside ASP page written by VBScript.

Thanks

[bateman_ap]

Use a select statement returning only one record and at the end use "ORDER BY NewID()"

ie

SELECT TOP 1 ID FROM YOUR_TABLE ORDER BY NEWID()

[abulhallaj]

My database is access and I use Microsoft.Jet.4 for connection to db ,SO NewID() function don't work . I think NewID()is only work on mysql.

I need a code that work on Access DB
I used the below code too:

i = int(rnd*100)+1
strSQL = "SELECT TOP 1 * FROM tbl ORDER BY RND(" & i & ");"

but it alwayes return the same record

[mattglet]

Try this:

Randomize()
i = int(rnd*100)+1
strSQL = "SELECT TOP 1 * FROM tbl ORDER BY RND(" & i & ");"

Randomize is a built-in VBScript function.

[Spooky]

Newid() also works on the latest jet 4 drivers

[abulhallaj]

I used all of this method but they don't work!

:(( :(( :(( :(( - :(( :(( :(( :((

[mattglet]

Are you getting errors, or are you continuing to have the same number show up?

[CaseyRyan]

How many records are in your table? This may also be a factor in what is showing up. If you don't have a lot, probabilty is higher you'll recieve the same record.

-=casey=-

[abulhallaj]

i have the same number for all records