filemanager
asked on
Selecting a random row in an MS Access database
I can't figure out why this SQL statement isn't working - it returns the same value every time.
SELECT Field1 FROM Table1 ORDER BY rnd()
I also tried
SELECT Field1 FROM Table1 ORDER BY rnd(NumericValue)
It returns the same result every time as well (not the same result as the first SQL statement, but the same one everytime the query is run.)
Any help would be appreciated.
SELECT Field1 FROM Table1 ORDER BY rnd()
I also tried
SELECT Field1 FROM Table1 ORDER BY rnd(NumericValue)
It returns the same result every time as well (not the same result as the first SQL statement, but the same one everytime the query is run.)
Any help would be appreciated.
Look up my comment here:
https://www.experts-exchange.com/questions/21713793/Return-only-1-random-record.html
It explains how and why.
/gustav
https://www.experts-exchange.com/questions/21713793/Return-only-1-random-record.html
It explains how and why.
/gustav
SELECT Field1, rnd() as RandNo FROM Table1 ORDER BY 2
Correction:
SELECT Field1, rnd([ID]) as RandNo FROM Table1 ORDER BY 2
Where [ID] is the autonumber column in the table
SELECT Field1, rnd([ID]) as RandNo FROM Table1 ORDER BY 2
Where [ID] is the autonumber column in the table
ASKER
I've tried all of your suggestions, and all of them return the same value every time :(
Hello filemanager
All of the above solutions worked for me. You need only two things: a) provide a field as parameter to force rnd() to recompute on each row, and b) make sure that the number is positive. That's all. Even Rnd(Val("1" & strJustAnyField)) works if you do not have an autonumber.
(°v°)
All of the above solutions worked for me. You need only two things: a) provide a field as parameter to force rnd() to recompute on each row, and b) make sure that the number is positive. That's all. Even Rnd(Val("1" & strJustAnyField)) works if you do not have an autonumber.
(°v°)
Sorry, but none of the methods using Rnd() "as is" directly in SQL will work.
With Access JET tables you have to use an external function as shown in the link provided above.
With SQL Server you have other options but that's another topic.
The link shows how to pick one (or more) unique random records.
In your case you request all records, thus it can simplified like this:
SELECT
Field1
FROM
Table1
ORDER BY
RandomNumber([ID] Is Null);
where ID is the primary key of your table.
/gustav
With Access JET tables you have to use an external function as shown in the link provided above.
With SQL Server you have other options but that's another topic.
The link shows how to pick one (or more) unique random records.
In your case you request all records, thus it can simplified like this:
SELECT
Field1
FROM
Table1
ORDER BY
RandomNumber([ID] Is Null);
where ID is the primary key of your table.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It may appear so, but if you run the query, record the resultset, close Access, then relaunch and run the query again, you will notice that the query returns the exact same resultset as before.
That's because Rnd() is not a function returning random numbers, only pseudo random numbers. To partially overcome this, VBA provides Randomize which you must call to obtain a unique sequence based on the current time which, of course, will be different each time you call Randomize. You might think that you could call Randomize from the query via a wrapping function - and you can do so - but it has no effect as it will be run in another scope than Rnd().
Thus, to obtain a different pseudo random sequence any time you run the query no matter if Access has been relaunched or not, you have to draw the sequence from an external helper function.
/gustav
That's because Rnd() is not a function returning random numbers, only pseudo random numbers. To partially overcome this, VBA provides Randomize which you must call to obtain a unique sequence based on the current time which, of course, will be different each time you call Randomize. You might think that you could call Randomize from the query via a wrapping function - and you can do so - but it has no effect as it will be run in another scope than Rnd().
Thus, to obtain a different pseudo random sequence any time you run the query no matter if Access has been relaunched or not, you have to draw the sequence from an external helper function.
/gustav
cactus_data,
Thank you for the clarification, I understand what you were getting at now. And since Randomize is not implemented as a function, there is indeed no other solution as the one you provide.
(°v°)
Thank you for the clarification, I understand what you were getting at now. And since Randomize is not implemented as a function, there is indeed no other solution as the one you provide.
(°v°)
If you only want to see ONE ROW and there is an autonumber column, you can use the following:
SELECT Top 1 Field1
FROM Table1
WHERE ID > (Rnd()*(Select Count(*) From Table1))
SELECT Top 1 Field1
FROM Table1
WHERE ID > (Rnd()*(Select Count(*) From Table1))
ASKER
Ok... I got the random SQL statement to work in MS Access, but when it's run from within VB it doesn't work... anyone know why?
Well, cactus_data did provide a full and detailed solution. But than again, there was no follow-up to the author's last succinct comment, mostly because the "why" was covered amply...
No objections from my part, but I would have recommended {http:#17974025} as the accepted solution.
(°v°)
No objections from my part, but I would have recommended {http:#17974025} as the accepted solution.
(°v°)
>there was no follow-up to the author's last succinct comment
That's why I recommended a refund... Lets see if the author rersponds.
That's why I recommended a refund... Lets see if the author rersponds.
ASKER
I ended up using puppydogbuddy's suggestion, in addition to adding:
intRandomNumber = .RecordCount
intRandomNumber = Int(intRandomNumber * Rnd) + 1
For i = 1 To (intRandomNumber - 1)
.MoveNext
Next
after I opened the recordset. For some reason it always chose the same record, so the code above picked a random record out of the recordset.
intRandomNumber = .RecordCount
intRandomNumber = Int(intRandomNumber * Rnd) + 1
For i = 1 To (intRandomNumber - 1)
.MoveNext
Next
after I opened the recordset. For some reason it always chose the same record, so the code above picked a random record out of the recordset.
ASKER
Clairification: I used a modified version of puppydogbuddy's suggestion.
filemanager,
You can select an answer yourself following these closing and grading guidelines:
https://www.experts-exchange.com/help.jsp#hs5
You can select an answer yourself following these closing and grading guidelines:
https://www.experts-exchange.com/help.jsp#hs5
> when it's run from within VB it doesn't work... anyone know why?
Sorry, missed that.
That's because you have to use similar code to what I've suggested also if you move your code to VB.
Indeed, a call to Randomize is mandatory. It not, you have your reason why:
> it always chose the same record, ..
/gustav
Sorry, missed that.
That's because you have to use similar code to what I've suggested also if you move your code to VB.
Indeed, a call to Randomize is mandatory. It not, you have your reason why:
> it always chose the same record, ..
/gustav
> I used a modified version of puppydogbuddy's suggestion
So no points for Richard.
/gustav
So no points for Richard.
/gustav
Oh... did you really intend to accept my answer? It seems my misconception has been corrected by gustav in detail!
(°v°)
(°v°)
ASKER
harfang, yes I meant to. I looked at the code I used and your suggestion was the closest to what I actually got working.
OK, then. Success with your project!
(°v°)
(°v°)
maybe you intended the following:
SELECT Field1 FROM Table1 ORDER BY rnd(Field1)