Random records in SQLServer 2005

Facing Problem while fetching records from database

Page 1 of 1

3 Replies - 2223 Views - Last Post: 05 November 2009 - 10:00 AM Rate Topic: -----

#1 illianafrazer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-September 09

Random records in SQLServer 2005

Posted 05 November 2009 - 09:04 AM

I am working on creating a quiz website. I will have 500 questions (which in future will change in quantity) in a db table.
The questions will be segregated into 5 categories Chemistry, physics, biology, maths and History. Here each category
has 100 questions.

My db table will have following fields as Q.no, questions, category and answer.

When a user opens my site and starts a quiz test he will be presented with 5 questions.

The five questions will be from the five categories.
If the same user now closes my site and reopens it, or some one else opens the site then the questions
which were displayed previously should not be displayed again instead I need to fetch another 5 random questions
from db table.
The above description is outline of what i need..

Here comes what i have tried:

I have tried the following query to display questions of different category.Here is the query

SELECT
	QC.QuestionCategoryId,
	QC.Category,
	Q.QuestionId,
	Q.Questiontext
FROM QuestionCategories QC (NoLock)
LEFT JOIN Questions Q (NoLock) ON Q.QuestionCategoryId = QC.QuestionCategoryId
WHERE Q.QuestionId IN (
	SELECT TOP 1 QuestionId
	FROM Questions Q (NoLock)
	WHERE Q.QuestionCategoryId = QC.QuestionCategoryId
	ORDER BY Q.QuestionId DESC
)
ORDER BY
	QC.QuestionCategoryId,
	Q.QuestionId DESC



When I run this query, questions from different categories are displayed.
But if my user reloads/chooses to write the exam again, how could I pick up another different set of questions
from the different categories and no questions
should be repeated from already finished questions set.

plz anyone help me to do this...


Thanks,
Illiana :)

Admin Edit: Please use code tags when posting your code. Code tags are used like so => :code:

Thanks,
PsychoCoder :)

Is This A Good Question/Topic? 0
  • +

Replies To: Random records in SQLServer 2005

#2 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Random records in SQLServer 2005

Posted 05 November 2009 - 09:32 AM

When wanting random records you can use the NEWID() built-in function (along with the RAND() Function) in SQL 2005 to accomplish this. Take a look at this example:

SELECT TOP 5
	QC.QuestionCategoryId,
	QC.Category,
	Q.QuestionId,
	Q.Questiontext
FROM 
	QuestionCategories QC (NoLock) LEFT JOIN Questions Q (NoLock) 
		ON Q.QuestionCategoryId = QC.QuestionCategoryId
ORDER BY
	RAND(CONVERT(VARBINARY(4),NEWID()))



Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#3 Trogdor   User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Random records in SQLServer 2005

Posted 05 November 2009 - 09:52 AM

check out this snippet from 2006: http://www.dreaminco.../snippet713.htm
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7288
  • View blog
  • Posts: 15,172
  • Joined: 16-October 07

Re: Random records in SQLServer 2005

Posted 05 November 2009 - 10:00 AM

Time stamp your questions.

Actually, just keep a count of how often you've served them up:
QuestionUsed
	QuestionId int
	TimesUsed int




Now, your select is scary. You may mean something like this:
SELECT
		QC.QuestionCategoryId,
		QC.Category,
		Q.QuestionId,
		Q.Questiontext
	FROM (
		SELECT QuestionCategoryId, MIN(QuestionId) AS QuestionId
			FROM Questions
			GROUP BY QuestionCategoryId
		) QS
		INNER JOIN QuestionCategories QC ON QS.QuestionCategoryId = QC.QuestionCategoryId
		INNER JOIN Questions Q ON Q.QuestionId = QS.QuestionId
	ORDER BY QS.QuestionCategoryId


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1