Random records (but with logic)

[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

[Bob Barrows [MVP]]

Luke wrote:

> 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.
>

I can't think of any way that does not involve using a cursor.

My suggestion would be to do TOP 6 instead of TOP 5, While processing the
returned recordset, , first check to see if the ID=4 record was retrieved,
if so, ignore the ID=9 record.

Sorry,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows]

Luke wrote:

> 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?
>

Wait a minute. What makes these two rows similar? Perhaps we could do
something with that ...

Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> because they are similar.

We need this information, rather than the fact that their IDs are 4 and 9.

A

[Luke]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...

> Wait a minute. What makes these two rows similar? Perhaps we could do
> something with that ...

Thanks,

There is nothing in the database that I can use to see if questions are
similar or not. My customer just told me to extract 50 questions (out of
100) but:

- Questions 4 and 9 should not be extracted together
- Questions 11 and 12 should not be extracted together
- If question 25 is extracted, then also question 26 should be extracted
- From question 50 to 60, only max 2 questions can be extracted
....

I like the idea to make a TOP 6 (in my first message the select was made
simpler), but I can I cycle through records and see if question 4 and 9 have
been extracted?

Thanks.

Luke

[Luke]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> ha scritto nel messaggio
news:#nUydQ5GEHA.576@TK2MSFTNGP11.phx.gbl...

> > because they are similar.

>
> We need this information, rather than the fact that their IDs are 4 and 9.
>
> A

Aaron, see my reply to Bob.
Thanks,

Luke

[Luke]

> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> ha scritto nel messaggio

> news:#nUydQ5GEHA.576@TK2MSFTNGP11.phx.gbl...

> > > because they are similar.

> >
> > We need this information, rather than the fact that their IDs are 4 and

9.

My Customer only told me not to return a recordset with both IDs 4 and 9
together (I can have a recordset with ID 4 only, or with ID 9 only, but NOT
with both IDs 4 and 9).

Thanks,

Luke

[Bob Barrows]

Luke wrote:

> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
> news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...

>> Wait a minute. What makes these two rows similar? Perhaps we could do
>> something with that ...

>
> Thanks,
>
> There is nothing in the database that I can use to see if questions
> are similar or not. My customer just told me to extract 50 questions
> (out of 100) but:
>
> - Questions 4 and 9 should not be extracted together
> - Questions 11 and 12 should not be extracted together
> - If question 25 is extracted, then also question 26 should be
> extracted

That means we are going to have to use a union to make sure 26 is always
selected.

> - From question 50 to 60, only max 2 questions can be extracted

Well, we're going to need more than 6 records - 17 should do it, plus the
unioned record from above.

> ...
>
> I like the idea to make a TOP 6 (in my first message the select was
> made simpler), but I can I cycle through records and see if question
> 4 and 9 have been extracted?
>
> Thanks.
>
> Luke

I will illustrate this using dynamic sql, but I urge you to use a stored
procedure instead.
This is untested air code:

dim conn,rs,sSQL,arData, arFiftyToSixty(1), bFourNine
dim bElevenTwelve,bTwentyFive, i

sSQL="SELECT TOP 17 0 as rank, NewID() as sortid, " & _
"id, <column list> FROM table UNION ALL " & _
"SELECT 1,26,id,<column list> FROM table " & _
"WHERE id=26 ORDER BY rank,sortid"

'instantiate and open connection using conn variable, then
set rs=conn.execute(" select top 16 ... ",,1)
if not rs.eof then arData=rs.getrows
rs.close:set rs=nothing
conn.close:set conn=nothing

for i = 0 to ubound(arData,2)
select case arData(2,i) 'the id value
case 4,9
if not bFourNine then
'write the data to the Response
bFourNine=true
end if
case 11,12
if not bElevenTwelve then
'write the data to the Response
bElevenTwelve=true
end if
case 50-60
if arFiftyToSixty(0) = "" then
arFiftyToSixty(0) = arData(2,i)
'write the data to the Response
elseif arFiftyToSixty(1) = "" then
arFiftyToSixty(1) = arData(2,i)
'write the data to the Response
end if
case 25
'write the data to the Response
'write the data in arData(,17) to the Response
end select
next


I would write a function that accepts the arData array and the row index to
write the data to the Response.

I have to go. Post back if you need more details

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Luke" wrote in message news:Byscc.19225$hc5.928552@news3.tin.it...
: "Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
: news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...
: - Questions 4 and 9 should not be extracted together
: - Questions 11 and 12 should not be extracted together
: - If question 25 is extracted, then also question 26 should be extracted
: - From question 50 to 60, only max 2 questions can be extracted

If 26 is extracted, must 25 also be extracted?
If 25 is extracted, must 26 be the next question or can they be in any
order?

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Mark Schupp]

If you have the option of modifying the database you would probably be
better of using "question pools". Similar questions would belong in the same
"pool" and you would only choose a single question from those pools. You
could also have a different type of pool where you choose a fixed number of
questions randomly from that pool.

You could then create a union of select statements, program logic, or a
stored procedure to collect the questions.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Luke" <nospam@nospam.com> wrote in message
news:4Cscc.19233$hc5.929396@news3.tin.it...

> > "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> ha scritto nel messaggio
> > news:#nUydQ5GEHA.576@TK2MSFTNGP11.phx.gbl...

> > > > because they are similar.
> > >
> > > We need this information, rather than the fact that their IDs are 4

and

> 9.
>
> My Customer only told me not to return a recordset with both IDs 4 and 9
> together (I can have a recordset with ID 4 only, or with ID 9 only, but

NOT

> with both IDs 4 and 9).
>
> Thanks,
>
> Luke
>
>

[Luke]

"Roland Hall" <nobody@nowhere> ha scritto nel messaggio
news:eTUi0M9GEHA.3576@tk2msftngp13.phx.gbl...

> "Luke" wrote in message news:Byscc.19225$hc5.928552@news3.tin.it...
> : "Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
> : news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...
> : - Questions 4 and 9 should not be extracted together
> : - Questions 11 and 12 should not be extracted together
> : - If question 25 is extracted, then also question 26 should be extracted
> : - From question 50 to 60, only max 2 questions can be extracted
>
> If 26 is extracted, must 25 also be extracted?

Yes

> If 25 is extracted, must 26 be the next question or can they be in any
> order?

Any order.
Thanks,

Luke

[Roland Hall]

"Luke" wrote in message news:tkAcc.21206$hc5.1004536@news3.tin.it...
: "Roland Hall" <nobody@nowhere> ha scritto nel messaggio
: news:eTUi0M9GEHA.3576@tk2msftngp13.phx.gbl...
: > "Luke" wrote in message news:Byscc.19225$hc5.928552@news3.tin.it...
: > : "Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
: > : news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...
: > : - Questions 4 and 9 should not be extracted together
: > : - Questions 11 and 12 should not be extracted together
: > : - If question 25 is extracted, then also question 26 should be
extracted
: > : - From question 50 to 60, only max 2 questions can be extracted
: >
: > If 26 is extracted, must 25 also be extracted?
:
: Yes

Dayum. I was afraid you were going to say that. I thought I was done
although I took a completely different approach for a solution. I'll post
when I'm ready.

: > If 25 is extracted, must 26 be the next question or can they be in any
: > order?
:
: Any order.

Oh good. ~sigh~ of relief.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Roland Hall]

"Roland Hall" wrote in message news:eOFlxKGHEHA.3832@TK2MSFTNGP10.phx.gbl...
: "Luke" wrote in message news:tkAcc.21206$hc5.1004536@news3.tin.it...
: : "Roland Hall" <nobody@nowhere> ha scritto nel messaggio
: : news:eTUi0M9GEHA.3576@tk2msftngp13.phx.gbl...
: : > "Luke" wrote in message news:Byscc.19225$hc5.928552@news3.tin.it...
: : > : "Bob Barrows" <reb01501@NOyahoo.SPAMcom> ha scritto nel messaggio
: : > : news:#AuRiL2GEHA.2224@TK2MSFTNGP12.phx.gbl...
: : > : - Questions 4 and 9 should not be extracted together
: : > : - Questions 11 and 12 should not be extracted together
: : > : - If question 25 is extracted, then also question 26 should be
: extracted
: : > : - From question 50 to 60, only max 2 questions can be extracted
: : >
: : > If 26 is extracted, must 25 also be extracted?
: :
: : Yes
:
: Dayum. I was afraid you were going to say that. I thought I was done
: although I took a completely different approach for a solution. I'll post
: when I'm ready.
:
: : > If 25 is extracted, must 26 be the next question or can they be in any
: : > order?
: :
: : Any order.

Hi Luke..

Here is what I came up with since I don't have Bob's expertise with SQL. I
chose to obtain the 5 random numbers, with the restrictions you mentioned,
(4 OR 9, 11 OR 12, if 25 then also 26, if 26 then also 25, 2 only from range
50-60) first and then you could just plug that into your SQL statement to
pick the records you wanted.

This requires your ID field be sequential and specific if it is
autogenerated. If not, then a separate field that can be controlled would
be required for the index.

You can test it here: [url]http://kiddanger.com/lab/random.asp[/url]
I made it visual so you can see what is returned from the initial sort of
100 numbers and what the result is after applying the restrictions. Just
refresh the page to see the different results.

HTH...

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]