Getting Random Records From Database For Quiz Game

Question

0.00/5 (No votes)

See more:

I want help from you to create a query. I am new in software career and i am trying to developing a KBC Type Quiz Game for Windows Mobile Application. For That i want try to fetch random records from database and if one record is appear at one time after that record will not appear again. I use This query "SELECT TOP 1 * FROM Quiz ORDER BY NEWID()" but records are repeat. My Table structure is given below.

ColumnName DataType

Id int
Que varchar(150)
Ans1 varchar(100)
Ans2 varchar(100)
Ans3 varchar(100)
TrueAns varchar(100)

http://i.stack.imgur.com/0kKPw.png

I try to create a Store Procedure also which is given below

SQL

DECLARE @counter int, @randno int, @uBound int, @lBound int

            SELECT @uBound = Max(Id) FROM Quiz
            SELECT @lBound = Min(Id) FROM Quiz

            SELECT @randno = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
            SET @Counter = 0

            WHILE @counter = 0
            BEGIN
                IF EXISTS(SELECT Id FROM Quiz WHERE Id = @randno)
                BEGIN
                    SET NOCOUNT OFF
                    SELECT * FROM Quiz WHERE Id = @randno
                    SET @counter = 1
                END
                ELSE
                BEGIN
                    SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0)
                END
            END

but i can't get success. My Table contain this fields Que, Ans1, Ans2, Ans3, TrueAns. Please Help me for this problem. And i want to also create a web-service which return all the records

Posted 3-Apr-13 20:16pm

Member 9762961

Updated 3-Apr-13 22:28pm

v3

Add a Solution

Comments

Maciej Los 4-Apr-13 2:35am

Please, show me the structure of your table and example data.
How many records should return your SP (only 1 in a time)? Do you have a table to store a questions with answers?

Member 9762961 4-Apr-13 2:41am

yes SP should return only one record at a time and i am create a field for answer in same table and i want whenever user give answer of question after that other question is show with it's options.

Maciej Los 4-Apr-13 3:07am

Please, see my answer. Example SP wasn't tested...

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pallavi Waikar · Answer 1 · 2013-04-03T20:33:00

check out this msdn article
http://msdn.microsoft.com/en-us/library/cc441928.aspx[^]
also try this ..store retrieve id in one variable and append in where clause for not in clause ...i want say something like below code...hope it wil

SQL

DECLARE @counter int, @randno int, @uBound int, @lBound int ,@notInId varchar(100)

SELECT @uBound = Max(Id) FROM Quiz
SELECT @lBound = Min(Id) FROM Quiz
SET @notInId=""
SELECT @randno = SELECT TOP 1 id, NewID() as Random FROM Quiz where id notin @notInId ORDER BY Random
SET @notInId=notInId+"'"+@randno+"'"
SET @Counter = 0

        WHILE @counter = 0
           BEGIN
            IF EXISTS(SELECT Id FROM Quiz WHERE Id = @randno)
                BEGIN
                    SET NOCOUNT OFF
                    SELECT * FROM Quiz WHERE Id = @randno
                    SET @counter = 1
                END
                ELSE
                BEGIN
                    SELECT @randno = SELECT TOP 1 id, NewID() as Random FROM Quiz where id notin @notInId ORDER BY Random
            SET @notInId=notInId+"'"+@randno+"'"
                END
            END

Maciej Los · Answer 2 · 2013-04-03T21:01:00

Solution 2

If the field for answer is in the same table, you need to change SELECT statement:

SQL

SELECT @counter=COALESCE(ID,0)
FROM Quiz
WHERE Id = @randno AND HasBeenAlreadyAnswered = False

You should randomize untill @counter=0.

In pseudo code:
1) generate random number (set @random)
2) check if question with ID = @random has been already answered
a) if yes, goto 1)
b) if not, exit while loop and return record

Below SP should works for you:

SQL

DECLARE @id INT
DECLARE @random INT

SET @id =0
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
WHILE (SELECT COALESCE(ID,0) FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered=True)=0
BEGIN
    SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
    SELECT @id = ID FROM Quiz WHERE ID = @random
END

--finally
SELECT *
FROM Quiz
WHERE ID = @id AND HasBeenAlreadyAnswered=False

That's all ;)

More about: WHILE[^]

[EDIT #1]
After your explanation, i see that your database design is bad. It's not possible to generate random question, if you do not store information about user answers... This is necessary to eliminate duplicates inside "random algorithm".
See examples:
Create a Quiz (for kids)[^]
Online Quiz[^]
Quiz - C# and .NET Secrets[^]
Online Exam in C# ASP NET[^]

At this moment solution sounds like: You need to redesign database.

Posted 3-Apr-13 21:01pm

Maciej Los

Updated 4-Apr-13 2:10am

v3

Comments

Member 9762961 4-Apr-13 3:08am

Thanks a lot i'll try your solution and try to resolve my problem.

Maciej Los 4-Apr-13 3:11am

You're welcome. Don't forget to vote (if solution was helpful) ;)

Member 9762961 4-Apr-13 3:22am

sure i have one question in this solution HasBeenAlreadyAnswered is some field you take for answered question?

Maciej Los 4-Apr-13 3:26am

Exactly! Remember, this is only an example. I don't see your database and table structure...

Member 9762961 4-Apr-13 3:28am

how can i send my table structure?

Maciej Los 4-Apr-13 3:54am

Just use "Improve question" widget.

Member 9762961 4-Apr-13 4:12am

I just improve my Question.

Maciej Los 4-Apr-13 4:25am

If i understand, your problem is now solved...

Member 9762961 4-Apr-13 4:36am

not still not solved i improve my question you can now show table structure also for that i also add image of structure

Maciej Los 4-Apr-13 4:38am

And example data... (10, 15 records)
I need it to test SP.

Member 9762961 4-Apr-13 4:49am

1 Capital of India Delhi Ahmedabad Bombay Delhi
2 Capital of USA NewJercy Washington NewYork Washington
3 Capital of UK Manchester Liverpool London London
4 Capital of Canada Ontario Manitoba Ottawa Ottawa
5 Capital of Australia Sydney Canberra Melbourne Canberra
6 Capital of Pakistan Islamabad Karachi Lahore Islamabad
7 Capital of Oman Nizwa Muscat Barka Muscat
8 Capital of China Shanghai Chongqing Beijing Beijing
9 Capital of Qatar Doha Al Khwar Al Wakrah Doha
10 Capital of Russia Saint Petersburg Moscow Novosibirsk Moscow
11 Capital of Saudi Arabia Mecca Jeddah Riyadh Riyadh
12 Capital of Singapore Singapore Sentosa Serangoon Singapore
13 Capital of Sri Lanka Colombo Kandy Galle Colombo
14 Capital of Thailand Phuket Bangkok Chiang Mai Bangkok
15 Capital of UAE Sharjah Dubai Abu Dhabi Abu Dhabi
16 Capital of Egypt Cairo Suez Alexandria Cairo
17 Capital of Afghanistan Kandahar Kabul Jalalabad Kabul
18 Capital of France Paris Avignon Strasbourg Paris
19 Capital of Germany Hamburg Munich Berlin Berlin
20 Capital of Spain Barcelona Valencia Madrid Madrid

Maciej Los 4-Apr-13 5:10am

And where you store user choices?

Member 9762961 4-Apr-13 5:25am

in my page i want to show question and it's related three options. when user click on right ans than it show new question show i want to store user data in one temporary table so when user start game again that time appear all questions again that time it not affect previous save records.

Maciej Los 4-Apr-13 8:10am

See my updated solution.

Mohamed Abbas · Answer 3 · 2014-09-14T22:50:00

DECLARE @id INT
DECLARE @random INT

SET @id =0
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
WHILE (SELECT COALESCE(ID,0) FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered=True)=0
BEGIN
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
SELECT @id = ID FROM Quiz WHERE ID = @random
END

--finally
SELECT *
FROM Quiz
WHERE ID = @id AND HasBeenAlreadyAnswered=False

Getting Random Records From Database For Quiz Game

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0