rbd523 wrote:

> Hey guys,
>
> First issue, it seems that FB 1.5.x doesn't have a RAND() function
> (??). I've read around and seen things that mention IB 5 having one
> (but it being broken). Basically I need one though...does anyone
> have a good UDF that emulates the RAND functionality? (returns a
> random number between two bounds)

As I mentioned some days ago,
you may use the UDF-library from
http://firebird.sourceforge.net/download/snapshot_builds/win/fb20.zip

It contains:

/*****************************************
*
* s 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 srand
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_srand' MODULE_NAME 'ib_udf';

/*****************************************
*
* r a n d
*
*****************************************
*
* Functional description:
* Returns a random number between 0
* and 1. Note: Use srand to seed the
* random number generator.
* This behavior has been changed.
*****************************************/
DECLARE EXTERNAL FUNCTION rand
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf';


You could replace your version of ib_udf.dll with the version from the
zip-file.

You will have to declare srand to your database and use this once to
seed the generator and use rand in subsequent calls,

> Secondly, I was looking into ways on _efficiently_ doing a multi-row
> select of NON-REPEATED "random" rows, for a given search criteria. I
> would like to do it all within PSQL (stored procedure). I've
> explored several options, like a stored procedure using a FOR
> SELECT ... DO construct (but that is row-by-row it seems), using
> cursors, etc. But these are mostly row-by-row random options, and
> with those, making sure a given randomly selected row isn't selected
> twice seems difficult.
>
> At this point I think the simplest way would be to do something like
> the following:
>
> SELECT DISTINCT * FROM footable WHERE ..... ORDER BY RAND()

select rand(),f.* from footable f where (Condition) order by 1;

For whatever use it may have :-)

Frank
--
Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets.
- J.K. Rowling