something you can do (as ugly as your solution :-) ):

each time you need a record: fill a 2 fields table with all values of
your primary key in the first field and a random value in the second
field and join the two tables order by the second field of the "random"
table ;-)

less ugly: if your PK is integer, get the greatest value and compute a
random value from 0 to this greatest value ;-)

Thierry

PenWin a écrit :

> Hi!
>
> In my application I want to select a random row. That is quite easily done
> with application support:
>
> number_of_rows = SELECT COUNT(*) FROM table
> random_value = something between 0 and number_of_rows-1
> row = SELECT FIRST 1 SKIP :random_value * FROM table
>
> I would rather do this in a procedure. Even that is reasonable, as long as I
> have UDF support:
>
> row = SELECT FIRST 1 * FROM table ORDER BY RAND()
>
> In my application I may not have any UDF support. I thought I could generate
> a semi-random value by using a numeric primary key as a seed to some simple
> hash function (e.g. something like PK*hugeprime MOD somewhatsmallerprime).
> That leads to a question, though: How to do a modulo in SQL? Is there an
> expression which returns the integer remainder after a division? So that I
> could use something like:
>
> row = SELECT FIRST 1 * FROM table ORDER BY somehugevalue%31
>
> I can always calculate modulo by
>
> modulo = hugevalue - (hugevalue/base)*base;
>
> But it seems somehow ugly :-)
>
> Pepak
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>