• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Randomising ResultSet

 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 80295
434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you randomise a "select" or a table at all?
You can put the results into a List and shuffle the List.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:

 
Campbell Ritchie
Marshal
Posts: 80295
434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you. Never knew about those functions.
 
shukla raghav
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
please try and help. Oracle is really troubling me to an extent...
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Advice taken will go through the details of sample function.
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

select top 100 * from foo order by newid()



when tried to execute it .its giving an error that " from keyword not found where expected"
 
Ranch Hand
Posts: 874
Android VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Space pants. Tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic