random records no duplicates

hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: <cfquery name="getquestion" datasource="#request.DSN#">   SELECT questions.questionid, questions.questiontext    FROM questions </cfquery>    <cfset displayRow = RandRange(1,getquestion.recordcount)> <cfoutput query="getquestion" maxrows="1" startrow="#displayRow#">#questiontext#</cfoutput> What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated.

Either keep a list of questions already asked and query again if it tries to give you one you've had before or keep a a copy of the query for each user and remove the row after you ask each question. On 3/29/12 1:21 AM, Akos Fortagh wrote: ----- Excess quoted text cut - see Original Post for more -----

Another way is to leave the already asked questions out of your new query each time: <cfquery name="getquestion" datasource="#request.DSN#">     SELECT questions.questionid, questions.questiontext      FROM questions      WHERE questions.questionid NOT IN (#listOfPreviouslyAskedQuestions#) </cfquery> <cfset displayRow = RandRange(1,getquestion.recordcount)> <cfoutput query="getquestion" maxrows="1" startrow="#displayRow#">#questiontext#</cfoutput> On 3/29/12 1:58 AM, .jonah wrote: ----- Excess quoted text cut - see Original Post for more -----

Agreed. Simple to store a list of already asked questions in the session scope. andy Another way is to leave the already asked questions out of your new query each time: <cfquery name="getquestion" datasource="#request.DSN#">     SELECT questions.questionid, questions.questiontext      FROM questions      WHERE questions.questionid NOT IN (#listOfPreviouslyAskedQuestions#) </cfquery> <cfset displayRow = RandRange(1,getquestion.recordcount)> <cfoutput query="getquestion" maxrows="1" startrow="#displayRow#">#questiontext#</cfoutput> On 3/29/12 1:58 AM, .jonah wrote: ----- Excess quoted text cut - see Original Post for more ----- question from it at a time. User clicks a button and the page reloads to give the next question. ----- Excess quoted text cut - see Original Post for more ----- be delivered twice? >> Any help would be greatly appreciated. >

There are more efficient ways of getting a single random record from the database. You are grabbing them all every time when you only need 1. SQL Server: SElECT TOP 1 q.questionid, q.questiontext FROM questions q ORDER BY NEWID() MySQL SELECT q.questionid, q.questiontext FROM questions q ORDER BY RAND() LIMIT 0,1 As for not showing a user the same question twice, you could store the questionIDs that they have seen in their session (or the questionIds that they havent seen... your call) and then filter those from your possible results. 1: Get a random record they havent seen SELECT TOP 1 q.questionid, q.questiontext FROM questions q WHERE q.questionId NOT IN <cfqueryparam cfsqltype="cf_sql_integer" value="#session.listOfUsedQuestionIds#" list="true" />) ORDER BY NEWID() 2: Add it to the list of questions they have seen listappend(session.listofusedquestions, thisQuestionQuery.questionId) 3: Display the question. .:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com hi there, wondering if someone might be able to help with this please. I have a simple questions table and I'm displaying one randomly generated question from it at a time. User clicks a button and the page reloads to give the next question. So far I have this simple bit working fine: <cfquery name="getquestion" datasource="#request.DSN#">   SELECT questions.questionid, questions.questiontext    FROM questions </cfquery>    <cfset displayRow = RandRange(1,getquestion.recordcount)> <cfoutput query="getquestion" maxrows="1" startrow="#displayRow#">#questiontext#</cfoutput> What would be the easiest approach to make sure that one question cannot be delivered twice? Any help would be greatly appreciated.

Massive thanks to everybody who helped especially to Bobby for an exact example, all works great. thanks again