Returning random records and NOT similar (random questions)

[Luke]

Hi,

I need to extract randomly 5 records from the table "Questions". Now I use

SELECT TOP 5 FROM Questions ORDERBY NEWID()

And it works. The problem is that I need an additional thing: if SQL
extracts record with ID=4, then it should not extract record with ID=9,
because they are similar. I mean, I'd like something to tell SQL that if it
extracts some questions, then it SHOULD NOT extract other ones.

How can I do it?

Thanks!

Luke

[Simon Hayes]

"Luke" <no****@nospam.com> wrote in message news:<%e********************@news3.tin.it>...

Hi,

I need to extract randomly 5 records from the table "Questions". Now I use

SELECT TOP 5 FROM Questions ORDERBY NEWID()

And it works. The problem is that I need an additional thing: if SQL
extracts record with ID=4, then it should not extract record with ID=9,
because they are similar. I mean, I'd like something to tell SQL that if it
extracts some questions, then it SHOULD NOT extract other ones.

How can I do it?

Thanks!

Luke

You need to define some logic to say why 4 and 9 are "similar". For
example, should ABS(x-y) > 10 be true for all possible combinations of
numbers in the result set? Or since you're retrieving questions,
perhaps they're in groups, ie. questions 1-20 are on the same topic,
21-40 on a different topic etc., and you want only one random question
from each topic?

Depending on what logic you decide, you might want to consider doing
this in a client application - if the first value you retrieve affects
which ones you can retrieve later, then a cursor-based solution might
be the only way to do it on the server side, and that will be slow. It
may be faster to use a client app which retrieves the maximum and
minimum values (or whatever data you need to reference in your logic),
and then applies your pseudo-random algorithm.

Simon