Thank you for the reply.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.
-----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: http://db.apache.org/derby/docs/10.1/ref/rrefcreateprocedurestatement.html Example: http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0408anderson/#cs_sp Hope the above helps. -Rajesh 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 Principal MSCC 312 952 8175