Return random row in MS Access?

I want to return a completely random row from a table in a MS Access database. how can I do this ??

pick one of the numeric fields in the table, e.g. the autonumber id if you have one

select top 1 * from yourtable
order by rnd(id)

will this work for any db (mysql, mssql)? i assume that it’ll also work if any of the id rows have been deleted.

no, each database’s “random” function works differently

rnd(id) simply uses the id value as a “seed” so that each row gets a different random number – if you don’t seed it, then all rows get the same random number

of course it will still work with gaps in the id numbers

This is what I am currently using, BUT, for example, I have a windows app whch runs the query every 1 second and every time I start the app it runs through the results in the SAME ORDER. So this cannot be completely randdom :wink:

“it runs through the results in the same order”?

there is only one result

try rnd(id*now())

lol, that’d definately mix it up a bit :slight_smile: I could have sworn I tried rnd(now()) yesterday but it didnt work. obviously I did it wrong :smiley:

Thanks dude :slight_smile:

[QUOTE=r937]“it runs through the results in the same order”?

there is only one result[QUOTE]

Dude, I have an application which, every 1 second runs a query using the prior mentioned method to randomize the result. and EVERY TIME I run the app and start running queries it gets them in the same order as the last time I ran the query!

hi guys im trying to get this to work but it keeps returning the same value

Here is my code so far:

	SQL = "SELECT TOP 1 * FROM Quotes ORDER BY RND(qID*now())"
	Set RS = DB.Execute(SQL)
	<% =RS("qQuote") %>

any ideas?


	SQL = "SELECT TOP 1 * FROM Quotes ORDER BY Rnd(TimeValue(Now())*-10000000*[qID])"
	Set RS = DB.Execute(SQL)
	<% =RS("qQuote") %>

just what i needed thanks!!