Saturday, January 21, 2006

Select a random row from a table with Firebird

After reading this page I noticed that Firebird was missing, so I had to add this little bit of info.
In order to select a random row from a table in Firebird you need to first install an UDF that provides the rand() function, which is ib_udf, it comes for free with the server, but it's not installed by default.
So execute the appropriate sql script to install a bunch of UDFs (ib_udf.sql and fbudf.sql scripts are located in C:\Programmi\Firebird\Firebird_2_0\UDF on my PC along with corresponding ib_udf.dll and fbudf.dll) or install only rand with:

DECLARE EXTERNAL FUNCTION rand
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf';

after installation you can finally select a random row:

SELECT
FIRST 1 *
FROM
COUNTRY
ORDER BY
rand();

No comments: