[AccessD] A2003: Random order

Shamil,

Very interesting. Susan sent me an article she'd written with Arthur Fuller
suggesting the same use of NEWID(). Thanks again.

Steve Erbach
Neenah, WI

On 10/26/05, Shamil Salakhetdinov <shamil at users.mns.ru> wrote:
>
> Steve,
>
> I should have written that this query is enough:
>
> select CategoryId, CategoryName
> from Categories
> order by newid()
>
> to get random order of source records.
>
> But RAND(Checksum(newid())) make generated values a kind of normalized.
> Additionally you can write:
>
> Convert(int, Floor(Rand(Checksum(newid()))*100+1))
>
> to get integer random IDs in the range 1-100. But take into account that
> they can be non-unique.
>
> For pure mdb +VBA solution you can use similar approach with a function
> like
> the one described here
>
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q176790
>
> to get random seed string, which can be converted to random ID similarily
> to
> MS SQL solution.
>
> Shamil
>
> ----- Original Message -----
> From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, October 26, 2005 9:37 PM
> Subject: Re: [AccessD] A2003: Random order
>
>
> > <<<
> > how I might speed this up?
> > >>>
> > Steve,
> >
> > In MS SQL you can use lightning fast query (Northwind db as example):
> >
> > select CategoryId, CategoryName
> > from Categories
> > order by RAND(Checksum(newid()))
>