Subject | Selecting a random row/Expressions in SQL |
---|---|

Author | PenWin |

Post date | 2007-08-29T08:37:48Z |

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

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