Getting random records

[Hussein Baghdadi]

Hi.
Suppose my database has 1000 recored.
How can I get ten random records from a database -to be displayed , say in the index page- ?

[Purushoth Thambu]

Which database are you using?. If you happen to use Oracle then it's simple

SELECT * FROM tab1 WHERE ROWNUM < 11

sometimes you may not get random rows if SQL Plan happens to use Index. You can try this

 SELECT * FROM (SELECT * FROM tab1 ORDER BY dbms_random.value) WHERE ROWNUM < 11

[Scott Selikoff]

You could always use the random api in java to select an id from x to y and then use that id to fetch the record from the database.

[Mahendra Shelke]

But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.

[Hendy Setyo Mulyo]

use the Java API random class to generate the random ids then query the record from database based on them.

[Jeanne Boyarsky]

Originally posted by Mahendra Shelke:
But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.

Which function does this refer to?

[Purushoth Thambu]

I don't understand how selecting random records through java is going to be effective. With java you need to

- Generate the Random numbers (What numbers?, How is it going to be used? You need to find out the domain boundary (max row or max value of numeric column)
- Bind these values to the SQL Query using IN operator. (Define SQL with 10 parameters and bind the values)
- execute query.

Isn't this approach bit expensive compared to doing it through DB?
[ October 21, 2006: Message edited by: Purushothaman Thambu ]

[Scott Selikoff]

Yes/No, if you have well structured data we some predefined information, the database call that actually retrieves the results will be a lot faster. Database calls are almost always more expensive than java calls so if you can reduce the database calls easily, do it. If the range is difficult to define and the data is scattered that's another story (although I imagine there's probably a clever way to do this in java still).

[Hussein Baghdadi]

I'm using MySQL 4.1
Actually, I was thinking about getting a random generated number, but is it the best way ?

[Stan James]

I'd prefer a sql solution, too, but would have to rely on db experts to hand me one.

Java approaches ... as mentioned above generating random keys in Java will only work if you know the database key distribution or generation algorithm. If you know the db has keys 1 through 1000 it's not too hard. If the db key is SSN, it will take a while for a RNG to get even one hit.

Check the performance of select the key from all rows, build an array, shuffle the array, query on the first n keys. This won't scale forever.

[Purushoth Thambu]

I am not mySQL guy, however when I looked at 4.1 doc's I see note on bug fix to make

  SELECT .. ORDER BY RAND()
  

work. Even if this doesn't work then you can use LIMIT functionality of MySQL to randomly pick the record.

    SELECT ... LIMIT ?,1

You need to bind ? with random number generated from Java (if you cannot find some other way to get this done on DB side).

PS: I am not sure what will be the cost of ORDER BY RAND() SQL will be. Since you need just 10 records if the SQL does a full scan then you need to compare between fetching one record at a time and RAND() method. It works differently in Oracle when you use ROWNUM < ? sort of queries.
[ October 23, 2006: Message edited by: Purushothaman Thambu ]