469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

Sort Order - random?

Is there a way to return a random sort order from a query?
Nov 12 '05 #1
5 10772
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.

Nov 12 '05 #2
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?

Nov 12 '05 #3
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?

Nov 12 '05 #4
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?

Nov 12 '05 #5
earlp
1
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]);
Apr 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by One's Too Many | last post: by
4 posts views Thread by Jon Hunt | last post: by
4 posts views Thread by Greg Brady | last post: by
3 posts views Thread by Bob Dankert | last post: by
7 posts views Thread by Steve Crawford | last post: by
2 posts views Thread by adrian.chandler | last post: by
2 posts views Thread by Chris | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.