random sorting each time query is run

Question

in an ms-access database i have a table

num   weight
1       12  
4       13
2       13
6       9
7       13

how can i write a query which will sort the table according to weight in descending order . but the numbers 4, 2 and 7 have same weight (13) , so they must be sorted randomly each time query is run.

any help appreciated.

Answer 1

Normally, your SQL would contain a random function of some sort (it looks like Access has the rnd() function for this).

So you could use:

select num, weight, rnd(num)
from tbl
order by weight desc, 3

This will let you see r for testing purposes. In a real query, you might just want to use something like:

select num, weight
from tbl
order by weight desc, rnd(num)

From this page:

When value is greater than 0, Rnd() returns the next random number.
When value is less than 0, Rnd() returns the same random number, based on value. If value occurs only once, you won’t notice this behavior. Access also resets seed, which means the sequence starts all over again.
When value is equal to 0, Rnd() returns the most recently generated random number

Update 1: I'm unsure as to whether rnd() is executed once in the following queries or once per row - the docs aren't clear. Comments seem to indicate the same results are being received for all rows which indicates it may be the latter. It may be that changing it to rnd(num) or rnd(abs(num)+1) will fix that problem. I'll have to check when I get to a box with Access installed.

Update 2: I've now tested this in Access 2007 and it does indeed give the same random value for every row when you use rnd(1). It does give a different value for rnd(num) each time you run the query and the individual rows get different values. So the query you need is:

select num, weight from tbl order by weight desc, rnd(num);

If you create a table with two Number fields and then run that query over it, you'll see that continual refreshing (with F5) will swap around the 2, 7 and 4 rows at random but leave the 1 and 6 rows in the same place since the weights of the first three are all 13 and the weights of the last two are 12 and 9 respectively.

I've updated the queries above to match this new information.

Answer 2

I think this will do the trick...

ORDER BY weight, Rnd()

Answer 3

When using RND() in Access Database Engine SQL from outside of the Access UI, the same sequence of random numbers will be used by each session (there is no native support for VBA's Randomize).

For example, connect to the source then execute SELECT RND(); three times in succession, you will get the following values:

0.705547511577606 
0.533424019813538 
0.579518616199493

Close the connection, connect to the source again then execute the same query again three times you will get the same three values as above in the same order.

In the knowledge that these values are predictable, we can demonstrate that a different value for RND() is used each time it is referenced. Consider this query:

SELECT RND() 
  FROM T 
 WHERE RND() > CDBL(0.6);

We know the first value in a new session will be 0.705547511577606, therefore the expression

RND() > CDBL(0.6)

will evaluate TRUE. However, the value 0.533424019813538 is repeated for every row in table T, a value which does not satisfy the WHERE clause! So it is clear that the value of RND() in the WHERE clause is different from the value in the SELECT clause. The full code is posted below.

Note I wondered if it may be possible to use the least significant portion of CURRENT_TIMESTAMP value generate a random number that could be used consistently through the scope of a single query and not be predictable for a session as RND() is. However, the Access Database Engine does not support it and its closest analogy, the NOW() function, does not have enough granularity to be useful :(

Sub jmgirpjpo()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat

    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      .Execute "CREATE TABLE T (T INT);"
      .Execute "INSERT INTO T VALUES (1);"
      .Execute "INSERT INTO T VALUES (2);"
      .Execute "INSERT INTO T VALUES (3);"

      Dim rs
      Set rs = .Execute("SELECT RND() FROM T WHERE RND() > CDBL(0.6);")
      MsgBox rs.GetString

    End With
  End With

End Sub