randomize

[gwrman]

I am trying to select a random record from a database for display.

each entry has an id number, but they may not be consecuitve...IE - records get added and deleted from time to time...

how can I display one at random? The code I have come up wiwth relies on the entries always being consecutively numbered , which is not feasible.

baiscally it goes like this:

1 - get number of records

2 - randomize that number

3 - select * where QID = that randomized number.

but you see....as records get deleted, any give id number could be gone.

 

[Saugilsr]

Here is a link with how to get a random record from different types of databases.

http://www.petefreitag.com/item/466.cfm

Or another way to do it is to pick a random number.
Then loop thru the records and stop on the random number.

psuedo:

while x < RAND
currnet = nextrecord
x + 1

But most databases have a specific SQL code to get a random record.

 

[PHV]

And what about something like this ?
SELECT TOP 1 * FROM yourTable ORDER BY Rnd(QID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tarwn]

It depends on what type of DB you have. Saugilsr's link shows you the common ones (except for MS Access). MS Access actually takes some work because, like so many other things, it doesn't work the way you would expect.
PHV's won't work for MS Access because you will get the same random number every time if your calling it over an ADO connection (while calling it in the DB will work as you would expect).
If I remember correctly, in order to make Random work for you with MS Access you need a way to seed it with a value that will not only be differant for every record, but that will change over time as well. I believe if you were to order by something like Random(yourIdField * Second(Now()) in an Access query you would actually get a random order everytime. Then it would just be a matter of doing a SELECT TOP 1 for your query.

-T

 

[gwrman]

Hmmm...interesting.

I do use an access db, and I DID get it working. Let me check when i get to the office, and I will post my solution...