Subject | Re: [firebird-support] Selecting a random row/Expressions in SQL |
---|---|

Author | Tetram Corp |

Post date | 2007-08-29T08:58:01Z |

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 :

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

>

>

>

>