db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: How to select a random record.
Date Tue, 03 Jan 2006 17:57:20 GMT
On Tuesday 03 January 2006 10:59 am, Legolas Woodland wrote:
> 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.
> *

I'm not sure where you were going with this.

No database has an effective method of pulling up a random record. RDBMS are 
not *designed* to do this .   

The reason I mention this is that I don't want anyone to consider this a 
limitation or a flaw within Derby. 

There may be a RANDOM() function within a database, however, this is only a 
piece of what is required to solve the problem.

There really hasn't been a good articulate description of the problem we are 
attempting to solve.

The use of stored procedures is not recommended. Yes you can use them, however 
there has not been enough information provided to show that a stored 
procedure is warranted.  By this I mean that you would have to show a 
constraint to justify the use of a stored procedure. SPs do have a cost 
associated with them. Depending on the RDBMS used, the cost vs performance 
will vary.

Going from memory, you're asking for a random category and then a random 
record from that category.  This is a two step problem.

If the types of categories are static, you may want to create a "look up" 
table which will make thing s a little faster. (Again this is app specific )

You would then get a count of the records and then generate a random number 
between 1 and n where n is the number of unique categories.

Then you can do one of the following:

1) Create a temp table containing the n records in that specific category and 
then fetch a random record from that list. A good idea if you're going to do 
this multiple times.


2) Get a count of the records that are in the category, and use a CURSOR with 
HOLD to fetch the nth record where n represents the random number between 1 
and the record count.

So its really a two step process. And either of these solutions would work.

Doing this in an "extended SQL" would be a bit problematic.
(Not saying that you couldn't do this, however, you'd have some issues that 
would need to be thought out.)

So for a quick and dirty way of doing this, stick with Java and JDBC.

Note that you really haven't created any boundries/constraints so its 
difficult to determine an effective solution.

But hey, what do I know? ;-)


Michael Segel
Michael Segel Consulting Corp.
(312) 952-8175 [mobile]

View raw message