in my application , i wish to fetch exactly one Question from each chapter to generate a final Exam question paper. Now since every chapter has several questions and i wish to randomly extract one question from each chapter from the Questions_Master table. Because it is a practice application i am using MS Access as database. i used following query to fetch data
This is the Exception that i am getting. I had to search the MS Access forum to know the function that is used by MS Acess to return random values. I am using it in the jdbc expression and it did not return unidentified function error so it means Rnd() method is recognized but i did not understand why is it returning Data Type misMatch
And did you concider this suggestion:
Does the query work when you execute it in MsAccess SQL window?
Joined: Aug 03, 2008
i have understood my mistake; the Rnd() expects an argument that is field name of the colums that is of type AutoNumber. Now i need to change my design and insert a column with auto number as the data type in my Database. ooooooh no..!!! isnt there any other way or i need to fetch ordered data and then write a code on the application end to do the same. But that will complicate my program logic.
The difficulty is that top 1 will return only one record, and not a record per chapter.
I have tried your rnd() example though, and that works: select * from Questions_Master ORDER BY Rnd(rno)
And my rno is of type Long Integer (Number)
You can solve your problem using two queries:
First get a list of allchapters:
select distinct Chid from Questions_Master order by chid
Then loop through all returned chapters, and for each Chid:
select top 1 * from Questions_Master WHERE Chid = ? ORDER BY Rnd(rno)
Joined: Aug 03, 2008
well that is certainly a solution but is there someway we could do it using a single query. I am not a database expert rather a layman. But i wanted to know cant we use group by clause in any way. what i know is that the group by works on a group of data why cant we make the Rnd() function work on each data block grouped by chid.