Sort Order - random?

[Greg Brady]

Is there a way to return a random sort order from a query?

[John Baker]

Greg Brady wrote:

Is there a way to return a random sort order from a query?

Create a function that returns a random number between 1 and the maximum
number of rows your query is likely to return. You can base this
dynamically on the tables you are querying if you wish. Have this
function create a column in the query. Sort by this column.

[Jeffrey R. Bailey]

In Access SQL it is possible to call certain VB Functions directly. In this
case the Rnd function can be called in a query and fed a seed value from the
data being sorted. Here is an example to randomly sort some invoice
numbers:

SELECT xTstSource.InvoiceNo, Rnd([InvoiceNo]) AS RndOrder
FROM xTstSource
ORDER BY Rnd([InvoiceNo]);

xTstSource is just a test table I created for the purpose of this post.

I have used the method that John puts forward and also used this one. This
one is simpler to implement and now is the one I use almost exclusively. I
stumbled across this solution in this forum and so must admit I am standing
on someone else's shoulders here. Thanks to all who answer here.

--
Jeffrey R. Bailey
"Greg Brady" <vz******@verizon.net> wrote in message
news:U0*******************@nwrddc01.gnilink.net...

Is there a way to return a random sort order from a query?

[John Baker]

Jeffrey R. Bailey wrote:

[cleaner solution snipped]

Noticed something interesting when I tried this out - I included the
random field in my query results. The results were indeed in random
order, but they did not look like they were sorted by the random order
field either.

Not that this really matters, but could it be being called twice
somehow? Once when it generates the value for the field, and once when
it does the sort?

[Jeffrey R. Bailey]

Yes, that is exactly what is happening, and obviously it only needs to be
called once. My bad, I pasted the example to quickly. Let's try again:

SELECT xTstSource.InvoiceNo
FROM xTstSource
ORDER BY Rnd([InvoiceNo]);
--
Jeffrey R. Bailey
"John Baker" <ba*****@ix.netcom.com> wrote in message
news:U_******************@fe2.columbus.rr.com...

Jeffrey R. Bailey wrote:

[cleaner solution snipped]

Noticed something interesting when I tried this out - I included the
random field in my query results. The results were indeed in random
order, but they did not look like they were sorted by the random order
field either.

Not that this really matters, but could it be being called twice
somehow? Once when it generates the value for the field, and once when
it does the sort?

[earlp]

Jeffrey, wonder if you might have a solution to this situation.
I use your example, using Rnd(IDNbr) where IDNbr is the autonumber
in my Access table. Using this SQL code on my local machine, and I
select the first record, which is always different. But accessing this
.asp file on my host's server, it always brings back the same record.
Earl


[quote=Jeffrey R. Bailey]Yes, that is exactly what is happening, and obviously it only needs to be
called once. My bad, I pasted the example to quickly. Let's try again:

SELECT xTstSource.InvoiceNo
FROM xTstSource
ORDER BY Rnd([InvoiceNo]);