DECLARE @rand_id int
SELECT @num_recs = count(*)
FROM table
SET @rand_num = Round(((@num_recs - 1) * Rand() + 1), 0)
DECLARE #mycursor cursor scroll static read_only for
SELECT id FROM Table
open #mycursor
fetch absolute @rand_num from #mycursor into @rand_id
close #mycursor
deallocate #mycursor
This is of course SQL Server specific. This also falls prey to the not so
random rand()...
jon
----- Original Message -----
From: "Daniel Lancelot" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Wednesday, May 23, 2001 8:46 AM
Subject: RE: [Fw: order by random]
> Yeah - there are many (valid/not so valid) ways of retrieving a single
> random record from a db.
>
> What does not seem as easy, is to retrieve a recordset sorted randomly,
> which displays in a different order each time it is is displayed (esp
useful
> with Select TOP N for retrieving a number of random records on each
> display.)
>
> Currently if I need to something like this then I need to select a record
at
> random in CF N times, checking on each itereation that the record I am
> selecting has not already been selected...
>
> What I'm after is pure SQL that I can put into a SQL Server view, and
select
> top N from wherever I need those random records...
>
> -----Original Message-----
> From: David Crowther [mailto:[EMAIL PROTECTED]]
> Sent: 22 May 2001 13:54
> To: SQL
> Subject: RE: [Fw: order by random]
>
>
> --1st query bob--
> SELECT FooID
> FROM tblFoo
>
> --2nd query--
> SELECT *
> FROM tblFoo
> WHERE FooID = #bob.FooID[RandRange(1,bob.recordcount)]#
>
>
>
> -----Original Message-----
> From: Daniel Lancelot [mailto:[EMAIL PROTECTED]]
> Sent: 22 May 2001 10:02 AM
> To: SQL
> Subject: RE: [Fw: order by random]
>
>
> I tried to do something similar in SQL, but found that the rnd() function
is
> not random enough - I.E. they were ordered in the same order each time. I
> also tried using getdate() as the seed, but as the whole query ran within
1
> ms, the results were still not random.
>
> If anyone has done this sucessfully (i.e - different results each time
they
> run) I would be interested...
>
> -----Original Message-----
> From: Tony Hicks [mailto:[EMAIL PROTECTED]]
> Sent: 22 May 2001 06:47
> To: SQL
> Subject: RE: [Fw: order by random]
>
>
>
> ----- Original Message -----
> From: Jim McAtee <[EMAIL PROTECTED]>
> To: CF-Server <[EMAIL PROTECTED]>
> Sent: Tuesday, April 10, 2001 11:24 AM
> Subject: Re: order by random
>
>
> > I believe the following will work in Access/Jet. There's no doubt a
> similar
> > function in SQL or other DBs.
> >
> > SELECT *
> > FROM mytable
> > WHERE mycolumn = '#myvalue#'
> > ORDER BY Rnd(mytableid)
> >
> > Use any numeric field in the Rnd() function, but a numeric primary key
is
> > ideal.
> >
> > Jim
> >
> >
> > ----- Original Message -----
> > From: "Javier Woodhouse" <[EMAIL PROTECTED]>
> > To: "CF-Server" <[EMAIL PROTECTED]>
> > Sent: Monday, April 09, 2001 6:13 PM
> > Subject: RE: order by random
> >
> >
> > > I had to do this and the only thing i could figure out was to create 5
> > > columms wich i updated regularly and would order by one of the five -
> > chosen
> > > at random... the columns themselves contained random numbders...
> > >
> > > That's about as close as i could come.. Hope it helps
> > >
> > > > -----Original Message-----
> > > > From: Tony [mailto:[EMAIL PROTECTED]]
> > > > Sent: Monday, April 09, 2001 6:24 PM
> > > > To: CF-Server
> > > > Subject: order by random
> > > >
> > > >
> > > > Hello Everyone,
> > > >
> > > > Is there a way to sort a column in random order? I thought
> > > > something like:
> > > >
> > > > SELECT * FROM MyTable
> > > > WHERE
> > > > MyVariable='#myvalue#'
> > > > ORDER BY RANDOM
> > > >
> > > > would output something like
> > > >
> > > > 1
> > > > 2
> > > > 3 the first time
> > > >
> > > > and then
> > > >
> > > > 1
> > > > 3
> > > > 2 (varying in order)
> > > >
> > > > but all it does is say error... If there isn't a way built
> > > > into cf/sql to do this does someone have their own method
> > > > that they'd be willing to share?
> > > >
> > > > Thanks,
> > > > Tony Hicks
> >
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
