Michael Segel wrote:
Why create a stored procedure when you are dealing with an application specific problem?
(Ok. He didn't say what language he was developing his app in or if he was even writing an app or if he wanted to do this as an extension to SQL. So I'm going to go out on a limb and assume he's writing an app)

The key is to generate a random number that will be representative of a record.  That is to say, if I create a random number, will there be a record that matches the number?

Assuming that you are using java as your app development language...
Java has a class for generating random numbers.

So create a list of distinct types of records, then you get a random number within 1 to n where n is the number of records. Then get a list of those records. Again, generate a random number to get a random record. (You did say that there could be multiple records of the same type...) 

That should solve your problem, however please note that your random number won't be truly random. But that's a different issue.
Thank you for the reply.
by your reply i should consider that Derby has no built-in mechanism for selecting a random record in an effective way.
now , should i create an stored procedure to select a random record , should i use normal sql command , or something else is correct way of doing the job.

-----Original Message-----
From: Rajesh Kartha <kartha02@gmail.com>
Date: Sun, 25 Dec 2005 19:07:38 
To:Derby Discussion <derby-user@db.apache.org>
Subject: Re: How to select a random record.

Maybe there are other ways to perform this, but the first thing that came to my mind was to create a 
 Java stored procedure.  This can use simple Java code to create the logic needed and return 
 a result set from the products table based on the categories and a generated random number.
 Syntax for creating procedures:
 Hope the above helps.

On 12/25/05, Legolas Woodland <legolas.w@gmail.com> wrote: Hi
Thank you for reading my post.
I have a Product table and a category table.
some of products exists in more than one category.
I should select a random product from product table (a record)  and it
should be from some certain categories. 
Now the question is :
What is best solution to select a random product which is in one or more
categories ( categories are  determined by users ).

Thank you

Sent via BlackBerry.

-Mike Segel
312 952 8175