Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

random resultsets

 
Dilshan Edirisuriya
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeff Ash
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Dilshan Edirisuriya
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your replies. Yes its a better way of doing that.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic