Ivan Prenosil wrote:

>>I have tried the obvious solution, and of course it does not work :-)
>>I declared the Rand UDF and tried
>>select
>> A.A, Rand()
>>from
>> A
>>order by
>> 2
>>
>>The order of the records are always the same since Rand Returns the same
>>value for all records, the result tend to be identical to a select
>>without an order by.
>>
>>But, What about if you try to write an UDF that returns random numbers
>>not based on system time (as rand do) and use the previous number as a
>>seed for the next one ?
>
> The Rand() function is fixed in FB2, I think you can use it in FB1.5 too.
> But the above statement will always read whole table !

The problem I have is that the 'model' provide by mysql and postgres is
ORDER BY RAND so just replacing with a UDF is not practical. I need to
'adjust' the SQL statement to give me something that at least tries to
return random records. My current thought is changing the ORDER BY RAND
into a AND unique_id IN ( random_id's ) and populate the list by a
stored procedure which uses skip with a RAND() - could that work ? Feed
the Stored Procedure with the number of results required, and get it to
work out the ranges for skip and rand based on the number of records in
the table.

> Lester, do you need "single select statement" solution ?
> Can you afford to create generator that would remember last used ID
> on each request ? Or just simply do something like
> select first 1 * from ... where id > gen(last_id, 7) order by id
> and reset generator if no record is returned ...

The sort of thing that is returned is a 'pick of the day', but that can
be a number of articles selected at random, or things like that. In
bitcommerce it returns - for example - six products from the entire
catalog which change every time you visit the front page. A random
selection from those in stock - just to complicate things ;)
Most just need a single result, are easier.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services
Treasurer - Firebird Foundation Inc.