How to fetch records randomly every time you query

[camarun20]

Hi ,
I constructed an MS access query in java to retrieve the rows in random.

The code snipet

Connection con = DriverManager.getConnection( database ,"","");
Statement s = con.createStatement()
strQry = "select top 10 S_No , Rnd(S_No) as exp from Questions order by Rnd(S_No) desc" ;
s.execute(strQry) ;


This is the query I used to fetch the records in random. When i tried executing this code several times it fetches only the same set of records each time.

Actually my requirement is , for every time you query this , it should fetch different records.


Please help me in this.

Thanks in advance
Arun kumar

[Stewart Ross]

Hi. Rnd() is evaluated once only in your expression, so it returns the same randomised number for all rows. You will need to fund another approach. One such is to create a small array, populate it with a list of random numbers between 1 and the number of records N, sort the list in ascending order, then loop through your recordset selecting the rows that correspond with the random numbers. I am not a Java programmer so am unable to advise further on the code you would need.

-Stewart

[camarun20]

Hi. Rnd() is evaluated once only in your expression, so it returns the same randomised number for all rows. You will need to fund another approach. One such is to create a small array, populate it with a list of random numbers between 1 and the number of records N, sort the list in ascending order, then loop through your recordset selecting the rows that correspond with the random numbers. I am not a Java programmer so am unable to advise further on the code you would need.

-Stewart

Hi Stewart ,

Thanks for finding your time in replying me. I understood your point well.

Let me explain you my requirement in brief.

I am in a process of creating a online test portal.
There is a table called "Questions" which has 4000 rows in it. When a user takes test , some 30 questions has to be picked up randomly and displayed to the user.

When a second user takes test another set of random questions and displayed to him. There can be repeated questions in both the occasions , but should not be the identical as the previous one.


I can randomly pick questions using java by getting all the 4000 rows and randomising using java. But my worry is the performance .

Is there any easy way to achieve this in access query?

Thanks
Arun kumar

[Stewart Ross]

Hi Arun. I am sorry to say there is no easy way to do as you ask in an Access query. Access queries are based on an implementation of SQL, which in turn is based on set arithmetic. As a consequence of its set-based nature SQL has no direct concept of record number or position.

The random selection of records relies on record numbering, either express or implied, along with the generation of a set of random numbers. In SQL there is no native function which will number the rows of a query sequentially, hence why loop processing in code is necessary (making use of implied record numbering by counting the records within the loop).

There are other approaches that do not involve any programming. One I use myself to select randomised samples of student data is to export the main query details to Excel (about 6000 rows), number the rows of the table, generate a set of random numbers in another Excel sheet, and use the VLookup function on the numbered table to return the details from the randomised rows.

-Stewart