File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes random resultsets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "random resultsets" Watch "random resultsets" New topic

random resultsets

Dilshan Edirisuriya
Ranch Hand

Joined: Apr 22, 2006
Posts: 299
Is there a way to generate the result set randomly? That means without the data base stored order i want to retrive those rows in random manner.

Dilshan Edirisuriya SCJP1.4, SCWCD1.4, SCBCD 5
Jeff Ash

Joined: Apr 07, 2007
Posts: 11
I have never come across a way to return a random order result set. One way to do this might be to do a SELECT returning only the primary key value for each row:

SELECT mytable_id FROM mytable ...

Then, put the results into a java.util.List. Then randomize the list (not too hard to do). Then, run a new query, iterating through the random order list of primary key values - something like this (assume your primary key is simple, one column, integer):

You could also just read the results into a java.util.List containing arrays representing each record, then randomize that.

It all depends on how fast it needs to be and how large your result sets might be.

Jeff Ash<br /><a href="" target="_blank" rel="nofollow">Excellentia Software</a>
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Does rand() do what you need?

select a, b, c, rand() from whatever order by 4

or maybe order by rand() ??
[ May 21, 2007: Message edited by: Stan James ]

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Dilshan Edirisuriya
Ranch Hand

Joined: Apr 22, 2006
Posts: 299
Thank you for your replies. Yes its a better way of doing that.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

The short answer is there's not way to do it in JDBC, its more of a java issue.

As previously mentioned, reading all the results into a data structure and randomizing will work with the catch you may retrieve a lot more results than you need (for large tables). In those cases, such as only need one random record out of thousands, it might be better to use a WHERE clause and pick random IDs. The problem with that though, is your IDs have to be numeric and consecutive (ergo no missing IDs between a range of numbers). In the case they aren't, you'd want to add a column to accomplish this.

[OCA 8 Book] [Blog]
I agree. Here's the link:
subject: random resultsets
It's not a secret anymore!