You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Legolas Woodland <le...@gmail.com> on 2005/12/25 21:46:56 UTC

How to select a random record.

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


Re: How to select a random record.

Posted by Legolas Woodland <le...@gmail.com>.
Rajesh Kartha wrote:
> 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.
>

Thank you for the help.
Does it bring an overload to my server ?
it is possible generate a random number (based on incremental record ID) 
and select that record , but it has a big draw back , what will happen 
if the generated number record was deleted ? should i issue another 
select and so on.
SQl server has a very neat solution for this

SELECT TOP 1 someColumn 
    FROM someTable 
    ORDER BY NEWID()

this sql will return a random record. i need something like this  , if 
it is possible.


Thank you again.
> -Rajesh
>
>
>
> On 12/25/05, *Legolas Woodland* <legolas.w@gmail.com 
> <ma...@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
>
>


Re: How to select a random record.

Posted by Rajesh Kartha <ka...@gmail.com>.
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 <le...@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
>
>