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

"Luke" <nospam@nospam.com> wrote in message news:<%ejcc.18367$hc5.868453@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