At 01:29 PM 7/12/2007, you wrote:

>I want to use rand() function to get a random row from a FORTUNE table.But I always get same a row when I reconnect to my database.
>The function description tell me:"Note the random number generator is seeded using the current time."
>Why I always get same row? How I change the seed?
>
>It is Firebird-2.0.3.12981-1-Win32.exe that I use.
>
>/*****************************************
> *
> * r a n d
> *
> *****************************************
> *
> * Functional description:
> * Returns a random number between 0
> * and 1. Note the random number
> * generator is seeded using the current
> * time.
> *
> *****************************************/
>DECLARE EXTERNAL FUNCTION rand
> RETURNS DOUBLE PRECISION BY VALUE
> ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf';
>
>
>SET TERM ^ ;
>ALTER PROCEDURE GET_RANDOM_FORTUNE
>RETURNS (
> FORTUNE Varchar(1000) )
>AS
>BEGIN
> SELECT FIRST 1 FORTUNE
> FROM FORTUNE
> ORDER BY rand()
> INTO :FORTUNE;
> SUSPEND;
>
>END^
>SET TERM ; ^

This statement cannot return any record from the table other than the first record that was created in the table. The ORDER BY clause *can* be an expression, but not an expression that has no relationship to any field in the table or the SELECT list.

You are probably getting confused because there is a syntax available that contains a number, e.g.
...
BEGIN
SELECT FIRST 1 FORTUNE, upper(fortune)
FROM FORTUNE
ORDER BY 2
INTO :FORTUNE;
SUSPEND
END
...

where the 2 here is a smallint that refers to a field in the SELECT clause. It is a typist-saver: underneath, the engine re-executes the expression calculation.

The rand() function returns a DOUBLE PRECISION number that is more than zero and less than none. If this statement does not actually cause an exception to be thrown (as I think it should), then the best it will do is parse the ORDER BY clause *at the time the SP is compiled* and store the result statically in the definition, as:

ORDER BY 0

-- which is meaningless. The DEGREE of a field is counted from left to right in the SELECT clause, starting at 1 for the first field. And an ORDER BY specification cannot be varied by a parameter or variable within the SP, either...

I expect you will find some creative way to fetch a random record from the table, but this isn't it....even though it would be really great to fire off a box of 12 Christmas crackers that have 12 *different* fortunes inside! ;-)

./heLen