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