jQuery in Action, 3rd edition
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

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
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="http://www.excellentiasoftware.com" 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: 3749

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: http://aspose.com/file-tools
subject: random resultsets
It's not a secret anymore!