This works ok for SQLServer (thanks to Craig Dudley)
select top 1 field1,
newid() as RandID
from tblName
order by RandID
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Simon Park Computer Systems Management, Inc.
Director, E-Business 205 South Whiting Street #201
Ph: 703-823-4300 x119 Alexandria, VA 22304
fax: 703-823-4301 http://www.csmi.com
> -----Original Message-----
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
> Sent: Monday, April 08, 2002 5:30 PM
> To: CF-Talk
> Subject: Re: Random Record
>
>
> Eric J Hoffman wrote:
> > What is the best way to get a random record from an table that has
> > frequent changes and updates...best practices or practical
> advice from
> > veterans? Randrange between 1 and recordcount won't work
> pulling a num
> > and then going against primary id..so somehow to get a "row"?
>
> Depends on your database. In PostgreSQL I prefer to use:
> SELECT *
> FROM table
> ORDER BY Random()
> LIMIT 1
> But in most other databases this doesn't work so I get the
> entire table
> and then use the RandRange() trick to show just 1 column.
>
> Jochem
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
