--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> 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.

Yuck, no not what I was suggesting, something more like (ignoring
syntax which I will not check):

create procedure RandomiseMyTable
returns
(
ID integer,
Seq double precision
)
as
begin
for
select ID, RAND()
from MyTable
into :ID, :SEQ
do
begin
suspend;
end;
end
^

Then to get 20 records:

select First 20 ID
from RandomiseMyTable
order by Seq

Obviously it is going to have to completely read and sort MyTable. I
suppose you could make it more generic using the execute procedure to
at runtime choose the table, and even have the above query in its own
stored procedure where you could ask for the first X amount rather
than 20, but I doubt that would be necessary.

>
> >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!)

Yes, after a few years of studying statistics, you get to the point of
scratching your head over what one means by random. For most people
though, they mean that they want the records returned, ordered in a
unpredictable manner and that each record should have an equal chance
of being returned at a given position.

Given that the seed for the RAND function is the time, one would have
to ensure that the precision of the timestamp used as the seed is
adequate to ensure that two or more sequential records do not end up
with the same seq except by random chance.

>
> 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.

You could also take that approach, but do it in a different table,
perhaps using CURRENT_TRANSACTION as part of the key. In that case you
would not need to consider lock conflicts, however you would need to
consider how you cleanup the table.


Adam