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
