Our exam engine keeps on chugging along, selling lots every year. Just won an award from a
board that judges teacher's software. OUCH. Hurt my arm while patting myself on the back.

I have an ugly solution to this problem, but I'd like a nicer one.

Let's say that I have 275 examination questions on a specific subject. I want to draw a
random set of 40. How I have been doing it is an horrific kludge:

Pick a random record
Mark that question number as used

Pick a random record, but reject if the question number has been used... and restart till
I get a random one.

There has GOT to be an elegant way to do this, and my brain hurts. Ideally, I'd like to be
able to feed as a parameter the number of questions to pull.

I do NOT need a solution to a problem of not giving the same student the same exam
question more than once. That has to have my kludge.

Yours in consuming aspirin.

Herb

Herb

> Our exam engine keeps on chugging along, selling lots every year. Just won
an award from a
> board that judges teacher's software. OUCH. Hurt my arm while patting
myself on the back.

Nice one!

> I have an ugly solution to this problem, but I'd like a nicer one.
>
> Let's say that I have 275 examination questions on a specific subject. I
want to draw a
> random set of 40. How I have been doing it is an horrific kludge:

It sounds like a pretty efficient way of getting a list of exam numbers but
as you are asking in the SQL group....

SELECT questionnumber,rand(1000000) AS rd INTO memory\temp FROM questions
ORDER BY rd;
SELECT rd,questionnumber from memory\temp
GROUP BY rd,questionnumber
TOP 40

John

Herb

A soon as I clicked send I realised it was over complicated!

SELECT rand(1000000) AS rd,questionnumber FROM questions
GROUP BY rd,questionnumber
TOP 40

John

John


I was going to say that wouldn't work because you could get the same random number several time but then I realised that it didn't matter since all you were doing was using the random number to sort the real numbers. Neat.

Roy Lambert

Roy

> I was going to say that wouldn't work because you could get the same
random number several time but then I realised that it didn't matter since
all you were doing was using the random number to sort the real numbers.
Neat.

Thanks.  I used a large random number to minimise duplicates as it could
skew the result to lower question numbers.

John

John,

> SELECT rand(1000000) AS rd,questionnumber FROM questions
> GROUP BY rd,questionnumber
> TOP 40

This method works but the result is statistically biased.
It doesn't guarantee an equal probability of being selected to all
questions because repeated values are possible for "rd", so questions
with a low "questionnumber" have a greater probability of being selected
than questions with a high "questionnumber".
Please note, however I do not have a better solution to propose

--
Fernando Dias
[Team Elevate]

Fernando

>This method works but the result is statistically biased.
>It doesn't guarantee an equal probability of being selected to all
>questions because repeated values are possible for "rd", so questions
>with a low "questionnumber" have a greater probability of being selected
>than questions with a high "questionnumber".
>Please note, however I do not have a better solution to propose

So in that case simply

SELECT rand(1000000) AS rd,questionnumber FROM questions
ORDER BY rd
TOP 40

??

Roy Lambert

"Herb (Kraft)" <herb@examprep.us> wrote in message
news:2F24E3CD-D34F-4949-914F-C2F1B9E6C927@news.elevatesoft.com...
> Our exam engine keeps on chugging along, selling lots every year. Just won
> an award from a
> board that judges teacher's software. OUCH. Hurt my arm while patting
> myself on the back.
>

Well done do u sell much to Europe ?

> I have an ugly solution to this problem, but I'd like a nicer one.
>

Easy peasey lemon squeezy. Make your problem one of the
questions in this years questionaire. Then next year use the
best answer.  In the meantime use the cobbled together version
u already have. After all it cant be bad if it won an award.

Good luck Rita

Roy,

> So in that case simply
>
> SELECT rand(1000000) AS rd,questionnumber FROM questions
> ORDER BY rd
> TOP 40

It looks good.
It can only produce unequal probabilities of selection in the very
unlikely case of getting more than 40 equal values and IMO the
probability of that event is so small that it's negligible.

--
Fernando Dias
[Team Elevate]

Roy

> SELECT rand(1000000) AS rd,questionnumber FROM questions
> ORDER BY rd
> TOP 40

Definitely better - don't know why I thought TOP could only be used with
GROUP BY.  Even if questionnumber remains ordered (??) it is only the
duplicates at the edge case ie top 40/41 where the ordering makes any
difference.  Knowing the application it is intended for I think the level of
bias is unlikely to be an issue.

John