--- In firebird-support@yahoogroups.com, "Myles Wakeham" <myles@...>
wrote:

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

same

> 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

loosely

> associated (ie. Share the same parent). I don't want to show any

favoritism

> 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

don't

> 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

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

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

Adam