I an understand the need for random order presentations - quizzes,
psychological tests, marketing displays, and games all depend on
selecting random datasets from a relatively static superset.

My approach would be a bit different than Helen's. Create an integer
column (call it RANDOM_SELECTOR) with an index and populate it with
sequential numbers. Generate random integers in the range of the values
populated in the RANDOM_SELECTOR column and select against this column.

It means that your maintenance process becomes less efficient and has to
ensure that this row is populated with sequential integers. But, you
can select a random dataset every pass through the selection process.

On Sun, 2006-06-04 at 12:03 +1000, Helen Borrie wrote:
> At 09:30 AM 4/06/2006, Adam wrote:
>
> >If that is honestly a requirement, then I do not think it can be
> done.
> >The engine itself isn't bloated with functions one may never use (as
> >you may guess from the file size)
> >
> >I can offer a suggestion that you consider the RAND function inside
> >IB_UDF, since this UDF ships with Firebird.
>
> Further to Myles' earlier reference, the RAND() function in IB_UDF
> *was* broken in versions of Firebird prior to v.1.5.2. Someone fixed
> it; so just make sure you have the latest version of the DLL.
>
> Myles had written:
>
>
> > > I was hoping that by using a RANDom number generator in the ORDER
> BY
> >clause,
> > > I might be able to define the sort order of the less specific data
> >set to be
> > > returned. I'd just like to have the rows returned in different
> >random order
> > > each time.
> > >
> > > Any ideas?
> > >
>
> Adam wrote:
>
> >I don't have time right now to write one, but you could create a
> >stored procedure that returns two parameters, the ID and a randomly
> >generated number.
> >
> >You could then select the first X records from that stored procedure
> >ordered by the randomly generated number. This will give you the PK
> of
> >20 random records, which you can use in a join or something to get
> the
> >fields you need.
>
> There seems to be confusion here. An ORDER BY clause is compiled
> into a stored procedure: you can't pass the ordering criteria as
> parameters. One option inside the SP is to use EXECUTE STATEMENT to
> construct the required SELECT statement at run-time. My gut feeling
> is that this would be a can of worms. There is no such thing as
> "random order" in a relational database. You either have a keyset
> that you order on, or you take the sets in physical retrieval
> order. The latter, although unpredictable, is not random, either.
>
> >It goes without saying that the stored procedure will do a table scan
> >of the table, so it would only work if the associated category list
> is
> >reasonably small, I wouldn't want to try it on a table with a million
> >records.
>
> Anything that used random numbers would require writing the
> randomized series to a keyed column somewhere. There is a whole lot
> of fuzziness in the problem description about just "how
> random".....I've been looking at the question for a couple of days
> and hoping it would just go away as "a bad idea" (which it is!)
>
> One suggestion is that you add a varchar column of suitable size to
> this table, called "OutputSequence" or something. Write a Before
> Insert or Update trigger that generates a fresh GUID for this column
> (there are UDFs around for generating these). Then, when outputting
> the child rows from your inner loop, order the FOR SELECT by this
> GUID.
>
> If you really need to have a *different* random order every time a
> user looks at it, then you can add noise to your application by
> running a SP that goes through the table setting PK_Value = PK_Value
> to trigger off the Before Update generation of fresh GUIDs for all
> rows and using exceptions to step over any rows that throw lock
> conflicts.
>
> Doable, but---Ugh.
>
> ./heLen
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
> SPONSORED LINKS
> Technical support
> Computer technical
> support
> Compaq computer
> technical support
> Compaq technical
> support
> Hewlett packard
> technical support
> Technical support
> services
>
>
> ______________________________________________________________________
> YAHOO! GROUPS LINKS
>
> 1. Visit your group "firebird-support" on the web.
>
> 2. To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> 3. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
> ______________________________________________________________________
>