Anyway, I started using this code. It seems to perform well enough for my
needs:

SELECT FIRST 1 t.fields
FROM table t
WHERE conditions
ORDER BY (t.key + seed)*4294967291-((t.key + seed)*4294967291/49157)*49157

t.key is an integer primary key from my table, seed is an integer which
comes from outside (could be a random number from my application, but a
GEN_ID(sequence, 1) works just as well as long as the value is the same in
both parentheses. Maybe it will be useful to somebody.

To aafemt: Not using UDF might be considered ugly, but at least it will work
on every server, no matter which (if any) UDFs are available.

Pepak

----- Original Message -----
From: "PenWin" <penwin@...>
To: "Firebird support" <Firebird-Support@yahoogroups.com>
Sent: Wednesday, August 29, 2007 10:37 AM
Subject: [firebird-support] Selecting a random row/Expressions in SQL


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