Randomize and Sample using SQL in Excel VBA

user_18814637 0
Viewed 43

I am using VBA for Excel and I have a workbook with a few tabs. I would like to randomize and pull a sample from each tab. An example of the code is below

   sql = "SELECT TOP " & myNum & " * " & _
   "FROM [Annual$] ORDER BY RND()"
      
   Debug.Print sql

Individually, both parts of this code works as intended. Together, however, I receive a random sample but I am no longer able to limit it. I am limited in VBA code with other options (to my understanding) but if there is a way to implement LIMIT instead of SELECT TOP then that could solve this but I don't believe Excel is capable of this.

Here is the debug print:

SELECT TOP 1 * FROM [Annual$] ORDER BY RND()
sampling vba excel sql
1 Answers

Apparently there must be some bug in the sql engine in use under the hood that adding that ORDER BY causes TOP to be ignored. Breaking the logic of the sql down to do the ORDER BY in a subquery and the TOP in the outside query appears to circumvent this bug:

SELECT TOP 1 * FROM (SELECT * FROM [Annual$] ORDER BY RND()) as sub;
user_22210010
Related