aspose file tools*
The moose likes JDBC and the fly likes Randomising ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Randomising ResultSet" Watch "Randomising ResultSet" New topic
Author

Randomising ResultSet

shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
here is the scenario : my application will access BOOKS written by AUTHORS. The result set will look as follows

AUTHOR | BOOK
----------------------
kathy | scjp
kathy | scwcd
kathy | HF ejb
kathy | HF java
horton | Beginning java
horton | Advanced Data Structures
horton | Regular Expressions in Java
.
.
.

Now my purpose is to randomly pick one record for each Author. I mean every time we access this resultset we want to randomly pick a different record i dont want that every time the resultset is created the first value is always the same. how do we do it?

would it be preffered approach to randomize the table created when we fire the query
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Can you randomise a "select" or a table at all?
You can put the results into a List and shuffle the List.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Yes, you can, at least if the database has mechanisms you can use. For example SQL Server has the newid() function which means you can write stuff like this:


and Oracle has the sample clause, which means you can do stuff like this:



JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Thank you. Never knew about those functions.
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
well here is more that i have learnt :- but havent tried it yet

Select COLUMN from TABLE
where COLUMN-n = "X"
order by RAND()
limit 1

this is for randomly selecting one record from a group of records. now my purpose is to modify the above to suit my requirement.
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
please try and help. Oracle is really troubling me to an extent...
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Where did you find the function rand()? Its not one Oracle supports as far as I am aware. Does it do something differernt from the sample function I mentioned earlier?
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
yes i think you are right . the one i said is for MySQL. but here is the oracle equivalent version also that i have found

Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

You can visit this link to know nearly all the main ones

http://www.petefreitag.com/item/466.cfm

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

OK, that will work, but like I said before why not use the sample clause? Is that not going to be way more efficient than using a subquery?
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
Advice taken will go through the details of sample function.
Vinnibabu singu
Ranch Hand

Joined: Aug 19, 2009
Posts: 30
select top 100 * from foo order by newid()


when tried to execute it .its giving an error that " from keyword not found where expected"
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

Vinnibabu singu wrote:
select top 100 * from foo order by newid()


when tried to execute it .its giving an error that " from keyword not found where expected"


This will not work in Oracle. "top" is not a function in Oracle.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Indeed. I suggest rereading my first post, top and newid() are a SQL Server specific. The keyword you are looking for is sample; that is the Oracle clause you need.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Randomising ResultSet