Randomize and Sample using SQL in Excel VBA-CodePudding

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()

CodePudding user response：

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;