Re: Random first record returned

On Thu, 27 Mar 1997 12:22:52 -0800, Melisa Mrazik <mmrazik_at_ix.netcom.com> wrote:

>I've been posed a unique SQL problem and hope that someone has a "quick"
>way of solving this required feature to an application (situation has
>been changed to protect the client's business but the concept is the
>same):
>
>From a set number of records in a table, HOTEL, a query will be sent with
>particular requirements (i.e. modern/historical, view/no-view, etc) and
>must return a matching set of records with the first record returned
>going to the application as the "preferred" hotel. If this same query
>with the same requirements is sent to the same table, a different first
>record must be returned to ensure randomness.
>
>I was thinking that a dynamic ORDER BY clause selecting one of 7 columns
>in the table to be ordered ascending or descending would give me at least
>14 combinations of randomness, but I don't know if this is the best way
>of doing this required functionality.
>
>Any and all suggestions appreciated!
>Thanks!

You can try:

select random.rand, hotel.* from hotel
where ......
order by 1;

and just fetch the first record. This would give you fairly good randomness...

Random would be defined as the following package:

create or replace package random
is

        pragma restrict_references( random, WNDS, RNPS );

        procedure srand( new_seed in number );

	function rand return number;
	pragma restrict_references( rand, WNDS  );

	procedure get_rand( r OUT number );

	function rand_max( n IN number ) return number;
	pragma restrict_references( rand_max, WNDS);

	procedure get_rand_max( r OUT number, n IN number );

end random;
/

create or replace package body random
is

	multiplier	constant number         := 22695477;
	increment	constant number        	:= 1;
	"2^32"		constant number			:= 2 ** 32;
	"2^16"		constant number         := 2 ** 16;
	"0x7fff"	constant number         := 32767;
	Seed		number := 1;
--
	procedure srand( new_seed in number ) 
	is
	begin
		Seed := new_seed;
	end srand;
--
	function rand return number
	is
	begin
		seed := mod( multiplier * seed + increment, "2^32" );
		return bitand( seed/"2^16", "0x7fff" );
	end rand;
--
	procedure get_rand( r OUT number ) 
	is
	begin
		r := rand;
	end get_rand;
--
	function rand_max( n IN number ) return number 
	is
	begin
		return mod( rand, n ) + 1;
	end rand_max;
--
	procedure get_rand_max( r OUT number, n IN number ) 
	is
	begin
		r := rand_max( n );
	end get_rand_max;

--
begin
	select userenv( 'SESSIONID' ) into seed from dual;
end random;
/



Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation