Random record set selection
-
Hi,
I am a newbee.
This question might not belong here, but I am sure you folks could help me out.
I have a 'questions' table with 25 questions and I have to select 10 questions at random from this table to display on the browser. I am using a primitive logic using random function.
I was wondering if you people (who are more experienced than me) could suggest some alternatives for this.
Thanks
Rai
-
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Copy this and paste it on new vb6 project and follow the list below:
1.add a command button
2.add a list box name it lstquestion
3.create a table name it question with questionID as auto increment by 1
4.uncomment the line DBConnection on line 93 5. add you Db_Name and your Server_Name on the connection string and you are done.
Option Explicit
Dim NumberArray(), DeleteArray(), I As Integer, AllDone As Boolean, Num As Integer
Dim getNumber As Variant
Private Sub ReSetNumbers(Max As Integer)
ReDim NumberArray(1 To Max)
For I = 1 To UBound(NumberArray)
NumberArray(I) = I
Next
AllDone = False
End Sub
Private Function RandomNumber() As Integer
Randomize Timer
RandomNumber = Int(Rnd * UBound(NumberArray) + 1)
End Function
Private Sub Command1_Click()
On Error GoTo BadRandom
'Check if all numbers are selected
Dim intCheck As Integer
Dim vValues As Integer
Dim sSQL As String
lstquestion.Clear
ReSetNumbers (Num)
For intCheck = 1 To 10
If AllDone Then
Dim Ans As Integer
Ans = MsgBox("All numbers are used. Do you wish to ReDo the Numbers?", vbYesNo, "All Numbers Used")
'Print a blank line And Reset the nembers
If Ans = vbYes Then Print: ReSetNumbers Num Else Exit Sub
End If
vValues = 0
'Get a Random number and add to the variable
Do While vValues = 0
vValues = NumberArray(RandomNumber)
If vValues > 0 Then
If Len(getNumber) > 0 Then
getNumber = getNumber & "," & vValues
Else
getNumber = vValues
End If
End If
Loop
'Create a second array
ReDim DeleteArray(0)
'Initialize Counter
Dim elecount As Integer
elecount = 1
'Cycle through the array
For I = 1 To UBound(NumberArray)
'Look for text box data
'Making both sides of the comparison upper
'Case removes case sensitivity
If NumberArray(I) <> Val(vValues) Then
'If the text data wasn't found create
'a new element in the second array
ReDim Preserve DeleteArray(elecount)
'Store the data from the first array
'In the second array
DeleteArray(elecount) = NumberArray(I)
'Increment the counter
elecount = elecount + 1
End If
Next I
'Check if last number
If UBound(NumberArray) = UBound(DeleteArray) Then
'Display
Print NumberArray(UBound(NumberArray))
'Set flag
AllDone = True
Exit Sub
End If
'This makes both arrays equal
NumberArray() = DeleteArray()
Next
'**********************************CHANGE HERE ******************************************************
'MsgBox "Last Values =" & getNumber
sSQL = "SELECT * FROM question WHERE questionID in (" & getNumber & ")"
Debug.Print sSQL
' uncomment this line and configure your Database connection string below
'DBConnection (sSQL)
getNumber = ""
'*********************************************************************************************************
Exit Sub
BadRandom:
Debug.Print Err.Source & " ( " & Err.Number & " ) " & Err.Description
Err.Clear
End Sub
Private Sub Form_Load()
Num = 25
ReSetNumbers Num
End Sub
Private Sub DBConnection(strSQL)
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cm As ADODB.Command
Dim strConn As String
strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB_NAME;Data Source=SERVER_NAME"
Set cn = New ADODB.Connection
cn.ConnectionString = strConn
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
lstquestion.AddItem rs.Fields("auther").Value & " - " & rs.Fields("question").Value
rs.MoveNext
Loop
End Sub
-
quote:
I have a 'questions' table with 25 questions and I have to select 10 questions at random from this table to display on the browser. I am using a primitive logic using random function.I was wondering if you people (who are more experienced than me) could suggest some alternatives for this.
Might want to consider using a good random number function. I'm including the code for such a function, which is my adaptation of something I found on this site (forget the author, sorry, but the idea wasn't mine) that uses the NEWID () function to come about as close to a good random number generator in T-SQL you can find. In the following code, the following is accomplished:
1. A temp table matching your spec is created, with a number from 1 to 25, and a question.
2. I wrap the random number generator with a loop that says, basically, don't leave this loop until you come up with something between 1 and 25.
3. Use this number as a key to your question.
Here's the random number generator:
CREATE FUNCTION f_rand (@f_seed UNIQUEIDENTIFIER)
RETURNS INT
AS
BEGIN
RETURN (CONVERT (INT, CONVERT (VARBINARY
, LEFT (CONVERT (VARBINARY, @f_seed), 4))))
END
Here's my dummy take on your question table:
CREATE TABLE #questions (seq INT NOT NULL, question VARCHAR (200) NOT NULL)
INSERT INTO #questions VALUES (1, 'Is it bigger than a bread box?')
INSERT INTO #questions VALUES (2, 'Is everybody happy?')
INSERT INTO #questions VALUES (3, 'Does the spearmint lose its flavor on the bed post overnight?')
INSERT INTO #questions VALUES (4, 'Does anybody really know what time it is?')
INSERT INTO #questions VALUES (5, 'Does anybody really care?')
INSERT INTO #questions VALUES (6, 'Do you know where you''re going to?')
INSERT INTO #questions VALUES (7, 'Do you like the things that life is showing you?')
INSERT INTO #questions VALUES (8, 'Do you have that in black?')
INSERT INTO #questions VALUES (9, 'Is there a method to your madness?')
INSERT INTO #questions VALUES (10, 'Do you have no shame, sir????')
INSERT INTO #questions VALUES (11, 'What do you have on tap?')
INSERT INTO #questions VALUES (12, 'Can you go any faster?')
INSERT INTO #questions VALUES (13, 'How about a date?')
INSERT INTO #questions VALUES (14, 'What do you think?')
INSERT INTO #questions VALUES (15, 'Does this happen to you a lot?')
INSERT INTO #questions VALUES (16, 'Am I making you uncomfortable?')
INSERT INTO #questions VALUES (17, 'Ever meet a celebrity?')
INSERT INTO #questions VALUES (18, 'What kind of car do you have?')
INSERT INTO #questions VALUES (19, 'If all your friends were to jump off a cliff, would you?')
INSERT INTO #questions VALUES (20, 'Can you show me some ID?')
INSERT INTO #questions VALUES (21, 'Do you prefer Scotch or Bourbon?')
INSERT INTO #questions VALUES (22, 'What Kind of Fool Do You Think I Am?')
INSERT INTO #questions VALUES (23, 'How Does It Feel?')
INSERT INTO #questions VALUES (24, 'Have you been a good little boy?')
INSERT INTO #questions VALUES (25, 'Do you mind???')
And here is my query:
DECLARE @rand INT
, @ctr INT
SELECT @ctr = 0
WHILE @ctr < 10
BEGIN
SELECT @ctr = @ctr + 1
SELECT @rand = 0
WHILE @rand NOT BETWEEN 1 AND 25
BEGIN
SELECT @rand = CONVERT (INT, RIGHT (CONVERT (VARCHAR, dbo.f_rand (NEWID ())), 2))
END
SELECT * FROM #questions WHERE seq = @rand
END
Edited by - Lee Dise on 11/04/2003 3:52:44 PM
-
quote:
1. A temp table matching your spec is created, with a number from 1 to 25, and a question.2. I wrap the random number generator with a loop that says, basically, don't leave this loop until you come up with something between 1 and 25.
3. Use this number as a key to your question.
How about just (as Frank indirectly suggested):
SELECT TOP 10 Question
FROM #Questions
ORDER BY NEWID()--Jonathan
--Jonathan -
quote:
How about just (as Frank indirectly suggested):SELECT TOP 10 Question
FROM #Questions
ORDER BY NEWID()
Well.. sheesh!!!!! I didn't know you could do that!
<grumble>
(That crashing sound in the background is the sound of Mr. Dise reluctantly learning something new...
) -
Following Mr. Dise posts is always something special and refreshing
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi rai_90
I guess i was not on target by giving u a unique random function in vb, sorry about that...
In SQL- I guess the newid() uniqueidentifier is one of the best solution for randomising your questions. In addition to my apology i thought i should give u a sql script for creating the table using the newid.
the newid() will create something like this for each record:
25C20982-D9F7-4873-8770-E30218B5CC3D this number is very unique.
CREATE TABLE Car
(
Car_id uniqueidentifier NOT NULL
DEFAULT newid(),
Make varchar(30) NOT NULL,
Model int NOT NULL ,
VIN varchar(6) NOT NULL
)
GO
INSERT Car
(Make, Model,VIN)
VALUES
( 'Toyota', '2002','TYT111')
INSERT Car
( Make, Model,VIN)
VALUES
('Toyota', '2002','TYT112')
INSERT Car
( Make, Model,VIN)
VALUES
('Toyota', '2002','TYT113')
INSERT Car
( Make, Model,VIN)
VALUES
( 'Mercedes-Benz', '2003','MBZ100')
INSERT Car
( Make, Model,VIN)
VALUES
('Mercedes-Benz', '2002','MBZ101')
INSERT Car
( Make, Model,VIN)
VALUES
('Mercedes-Benz', '2002','MBZ102')
INSERT Car
( Make, Model,VIN)
VALUES
('Mercedes-Benz', '2002','MBZ103')
GO
--to compare 'ure results, run this queris
--multiple times.
select * from car
select top 3 * from car order by newid()
-
What about
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
generate a random number, then use to retrieve data .... ?
)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply