Subject Re: [firebird-support] Re: Selecting a random row/Expressions in SQL
Author PenWin
> --- In, "PenWin" <penwin@...> wrote:
>> 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
> I don't see how unless you have a LOT of fields ;)
> You seem confused over the ORDER BY clause. It needs either a field
> name or an ordinal number referring to the column in the select you
> want to order on (where 1 is the first column).
> According to your above query, Firebird would raise an exception if
> that big expression down the bottom returned anything other than 1.

Actually, no, it works.

> I think you may mean something like.
> SELECT FIRST 1 t.fields
> FROM table t
> WHERE conditions
> AND t.Key >= seed*4294967291-(seed*4294967291/49157)*49157
> order by t.fields
> ... and be careful about integer divisions in SQL. It probably isn't
> returning the number you expect. Either cast as some form of numeric
> or add a .0 to the numerator or denominator.

I _want_ integers. With floats this wouldn't work.