Help with getting random results in a query.

odin1701 said:

I need some help getting random results in a query.

I have this so far, and it works, but it is slow.

SELECT TOP 1 tblMain.Field1, tblMain.Field2, Rnd(tblMain.Field2) As Expr1, ... FROM tblMain WHERE tblMain.COMPLETED=False ORDER BY Rnd(tblMain.Field2) DESC;

This does a great job actually finding a random record.

The problem is that it is slow. I have about 77,000 records currently. I will need to add I would say at least another 250,000 records to this database. So the speed will be very slow.

Is there a way to just get a quick query of say 100 records, then run the randomize query on just those records in order to choose one?

Or could I use an autonumber field set to random and have it somehow grab one of those?

My issue is that a form I have for working on these grabs one, but if I just use the TOP 1 function it will grab the same record over and over. This is an issue because only one person should work on the record at one time. So sometimes it becomes possible for more than one person to select the same record, though I tried to prevent that. I have a yes/no field which is set to yes when someone opens the record, and then the query should ignore that record. But so many people are using it, it is still happening.

Any ideas would be appreciated. Thanks!

Click to expand...

odin1701 said:

When a record is completed, that completed field needs to be used to exclude those records from being selected.

I don't have record ID's. Well I do. There are two fields which hold parts of an account number. Both of those fields are set to primary keys so as to prevent duplicates from being imported. There should only be one of each account number. I could ADD an autonumber, but it can't be a key.

Click to expand...

SELECT id,fld2, fld3 from main
    where id = round(77000*Rnd) +1
    and exists (
      SELECT main.id
      FROM main
      WHERE (((main.COMPLETED) = FALSE)));

odin1701 said:

The account numbers have a range, but to get the unique range you would have a possible value from 4,000,000,000 to roughly 635,000,000,000. The second part of the account number is not necessarily uniqe but even then you would be talking about a range of anywhere from 900,000,000 to 999,999,999.


And of course I won't have all possible or existing account numbers in this database either. Just some of them. So randomly picking a valid account number won't work.

Click to expand...

jdraw said:

Code:

SELECT id,fld2, fld3 from main
    where id = round(77000*Rnd) +1
    and exists (
      SELECT main.id
      FROM main
      WHERE (((main.COMPLETED) = FALSE)));

Click to expand...