>>Sorry. I wanted to clarify this point, just in case you didn't know.
>>
>>Testing repeatedly for rand(-1) will give you the same "random" number several times in a row, before goint to the next one, so that can probably be discarded.
>>
>>Is it possible to call a UDF from the SELECT statement? If yes, a workaround might be to declare a global variable, to figure out whether the UDF was already invoked before. If the answer is "no", initialize the random number generator (rand(-1)). In any case, just return rand().
>
>This is a one call with the OleDb provider. So, I need to find a way to achieve that within the SQL. Yes, RAND(-1), within the SQL is a no go. As you said, it always return the same random generator for each record so this doesn't bring me any further. With OleDb provider, I don't know if it is possible to set the RAND(-1) in memory before launching any SQL calls into it.
Maybe a fake select first... something like
select ..., rand(-1) as whatever, ... where .f.;
union select ...{your original select here}
Or if the "where .f." part would make the rand(-1) to be skipped, maybe you could include "rand(-1)>-1" somewhere in the beginning of the where clause of your original select - assuming the where is calculated before the order by and top n are executed.