okay smartie pants, try this! Works in MS SQL 7.
declare @rand_id int
while @rand_id is null
BEGIN
set @rand_id = (select id
from table
where id = rand() * (select max(id) from table) + 1)
END
select * from table where id = @rand_id
Alan McCollough
Web Programmer
Allaire Certified ColdFusion Developer
Alaska Native Medical Center
> -----Original Message-----
> From: Bert Dawson [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, December 12, 2000 10:57 AM
> To: Fusebox
> Subject: RE: Random record?
>
> definately more elegant and efficient than mine, and more 'random' (and
> elegant) than Nat's suggestion
> ;)
> However....what about when a row has been deleted, ie there is now id
> which
> matches the condition?
> I suppose then it gets a bit tricky with a stored procedure loop thing....
>
> Bert
>
> > -----Original Message-----
> > From: McCollough, Alan [mailto:[EMAIL PROTECTED]]
> > Sent: 12 December 2000 19:28
> > To: Fusebox
> > Subject: RE: Random record?
> >
> >
> > Okay, here is pure SQL solution stolen right from www.swynk.com:
> >
> > select *
> > from foo
> > where id = rand() * (select max(id) from foo) + 1
> >
> > Alan McCollough
> > Web Programmer
> > Allaire Certified ColdFusion Developer
> > Alaska Native Medical Center
> >
> > > -----Original Message-----
> > > From: Nat Papovich [SMTP:[EMAIL PROTECTED]]
> > > Sent: Tuesday, December 12, 2000 10:36 AM
> > > To: Fusebox
> > > Subject: RE: Random record?
> > >
> > > But what would be even more elegant is the random choice to
> > happen in the
> > > SQL layer. Anyone have a DBA handy? The only way I can
> > figure is to have
> > > another column in your table with a count of the nuber of
> > time the row has
> > > been used, which gets updated when it DOES get used, and
> > the select gets
> > > the
> > > row with the lowest viewcount. It would be nice to be in a
> > stored proc.
> > > This
> > > would eliminate the entire recordset coming across ODBC to
> > CF where CF
> > > only
> > > wants one row. If you only have 100 rows in the table, then it's no
> > > biggee,
> > > but if you have 10,000....
> > >
> > > Nat Papovich
> > > Webthugs Consulting
> > > ICQ 32676414
> > > "People don't know the bandwidth of a FedEx truck full of
> > diskettes."
> > > -William Gibson
> > >
> > >
> > > > -----Original Message-----
> > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > > > Sent: Tuesday, December 12, 2000 6:36 AM
> > > > To: Fusebox
> > > > Subject: Re: Random record?
> > > >
> > > >
> > > > Please disregard my last note. Bert's solution is way
> > more elegant.
> > > >
> > > >
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
