Re: Pick a random record using SQL

From: Mark Rosenbaum <mjr_at_netcom.com>
Date: 1996/12/26
Message-ID: <mjrE30zKE.J2H@netcom.com>#1/1

In article <59tgta$k8q_at_eng-ser1.erg.cuhk.edu.hk>, cheung yat tung <ycheung3_at_ie.cuhk.hk> wrote:
>Dear All,
>
>I need to randomly retrieve a record from a Oracle table using SQL. Is
>there any way to do this?
>

Tony,

Do you want a truely random number or a pseudo random number. The difference is that a pseudo random number appears random but is a string of numbers that is repeatable. True random is not repeatable. pseudo random numbers are typically used for development so that when (not if) bugs are encountered the program can be rerun to find out why.

Either way get a random number (either real or psuedo) from an OS run program (man rand on a UNIX system) and then try

select * from view
where row_nunber = random_number;

after
create view as

select	*,
	rownum row_number

from table;

My syntax is a little rusty but I'm sure you can look it up. The view is necessary because the strange behavior of rownum.

Hope this helps

Mark Rosenbaum			Otey-Rosenbaum & Frazier, Inc.
mjr_at_netcom.com			Consultants in High Performance and
(303) 727-7956			Scalable Computing and Applications
POB 1397			ftp://ftp.netcom.com/pub/mj/mjr/resume/

Boulder CO 80306 Received on Thu Dec 26 1996 - 00:00:00 CST