• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Select Random result from Resultset

 
Atul Mishra
Ranch Hand
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a requirement to randomly select a string from a resultset. How do I do it ?

I have a resultset which selects 2 strings from Oracle database.

Like this: Select a,b from table a where xxx

Lets say my query gives me 5 record sets.
For each transaction I want to retrieve results randomly, like if first transaction output is 1 recordset then second should be 2 recordset or 3 record set etc[random outputs]
Can I achieve this in Java/JDBC ?

I know I can get Random results using dbms_random package in Oracle, but doing this I have to do two queries.

Please help

thanks
 
Raj Chila
Ranch Hand
Posts: 128
Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you may not have such a feature in JDBC but you could generate a Random number in the selected column range and use that as the column number to read from the row.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33693
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think RajaniKanth meant row instead of column. I prefer that approach as it makes the SQL database independent.

If you are ok with something Oracle specific, see this list. Read the comments below it as they list some variations there.
 
Atul Mishra
Ranch Hand
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne and Raj,

Thanks for the comments. I am using the ORACLE approach right now, using dbms_random package. Which is the preferred approach ?

If Java, random way is the preferred approach, which is the API which I should use. Any samples, or links appreciated

Thanks.
 
Raj Chila
Ranch Hand
Posts: 128
Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne, I meant row, which means I misunderstood the question in the first place, but as you said this this approach can be used in both scenarios.

Atul, here are the steps to do it.
1. Use the Math.random() method to get a random value.
2. get the result set.
3. if you dont already know the number of rows then use last(),getRow() on your result set object to get the number of available number of rows. and then calculate a random row number with the help of the random number calculated at 1.
4. use absolute(with number calulated at 3)

your cursor is at a random row.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33693
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Raj B",
You appear to have changed your display name from a valid one to an invalid one. In particular, a last name is required - not just a last initial.

Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

Thanks,
Jeanne
Forum Bartender
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic