--- In firebird-support@yahoogroups.com, weiwufeng <blackspace@...> wrote:

>
> Why I get some differ rows when I call the SP many times in a

connection?

> When I reconnect to the database,why the SP return a same row again?
>
> SET TERM ^ ;
> ALTER PROCEDURE GET_RANDOM_FORTUNE
> RETURNS (
> FORTUNE Varchar(1000) )
> AS
> BEGIN
> FOR SELECT FIRST 1 FORTUNE
> FROM FORTUNE
> ORDER BY rand()
> INTO :FORTUNE
> do
> begin
> end
> SUSPEND;
>
> END^
> SET TERM ; ^
>

Because you misunderstand what ORDER BY [number] does. It does not do
what you are trying to achieve.

The [number] represents one of the columns in the select, the first
field in the select is 1, the second is 2 and so-on. So the query

select a,b,c
from d
order by 2

Is exactly the same as

select a,b,c
from d
order by b

It does not return the records of d in a 'random' order.

You only have one column in your procedure, so any value returned
which is not 1 is going to return an error "Invalid column position
used in the ORDER BY clause".

Once you realise it can not be done that way, you will hit another
problem with the random number generator. Because the seed is based on
the time, you will find that it does not vary within an operation.

For example, try

select rand()
from sometable;

You may want to try another approach where you write your own UDF, and
provide an additional seed parameter which can be provided by a
generator, and use this in conjunction with the current time to be
less predictable within an operation.

Adam