Subject Re: Does FB 1.5 support RAND function?
Author Adam
--- In, "Myles Wakeham" <myles@...>
> > Again, a simple example of the sort of thing you are trying to do will
> > make it easier to suggest a good way of doing it.
> What I am trying to do is to force the sort order of rows returned
from a
> stored procedure to be randomly sorted. Basically what is happening
is that
> a user is browsing a list of records in a category. However in
addition to
> return the rows that are members of that category, I also want to
return a
> set of rows that are members of associated categories (ie. Share the
> parent), but AFTER the first set of matching rows are returned (this is
> where my previous question about joining sets, which I have been
able to get
> working with a UNION statement per the suggestion).
> However the order that the second set of rows are returned is important.
> The first set should be the matching rows to they category that the
user has
> selected. Then a random selection of rows from categories that are
> associated (ie. Share the same parent). I don't want to show any
> with the second set of data being returned, hence the need for
random order.
> I don't want to have the client application handle the sort order. I'd
> prefer a stored procedure to do the work. UDFs are out of the question
> because I don't really want any external software involved due to
> performance issues (and that this database is running on Linux and I
> want to have to cross compile code, etc.).

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.

> I was hoping that by using a RANDom number generator in the ORDER BY
> 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?

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.

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