CODEWITHSUNDEEP
sql
BycullaB
BycullaB

Reputation:

SQL Statement that never returns same row twice?

Requirements: I have a table of several thousand questions. Users can view these multiple choice questions and then answer them. Once a question is answered, it should not be shown to the same user again even if he logs in after a while.

Question

How would I go about doing this efficiently? Would Bloom Filters work?

Upvotes: 0

Views: 415

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294317

BTW, if you cannot create a table to track questions shown then you can query the questions in a deterministic order (ie. by Id or by Title) and select each time the one with the rank higher than the last rank shown (using ROW_NUMBER() in SQL Server/Oracle/DB2, or LIMIT in MySQL). You'd track the last rank shown somewhere in your user state (you do have a user state, otherwise the whole question is pointless).

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294317

You insert each question shown into a log table with question_id/user_id, then show him the ones that don't match:

SELECT [TOP 1] ...
  FROM questions 
  WHERE question_id NOT IN (
   SELECT question_id
   FROM question_user_log
   WHERE userd_id = <current_user>)
[ORDER BY ...]

or

SELECT [TOP 1] ...
   FROM questions AS q
   LEFT OUTER JOIN question_user_log AS l ON q.question_id = l.question_id
     AND l.user_id = <current_user>
   WHERE l.question_id IS NULL
[ORDER BY...]

after you show the question, you 

INSERT INTO question_user_log (question_id, user_id) 
  VALUES (<question shown>, <current_user>);

Upvotes: 0

MyItchyChin
MyItchyChin

Reputation: 14031

Create a QuestionsAnswered table and join on it in your select. When the user answers a question, insert the question ID and the user ID into the table.

CREATE TABLE QuestionsAnswered (UserID INT, QuestionID INT)

SELECT *
FROM Question
WHERE ID NOT IN (SELECT QuestionID 
                 FROM QuestionsAnswered
                 WHERE UserID = @UserID)

INSERT INTO QuestionsAnswered
(UserID, QuestionID)
VALUES
(@UserID, @QuestionID)

Upvotes: 4

JNappi
JNappi

Reputation: 1585

Create a many-to-many table between users and questions (userQuestions) to store the questions that have been answered already. Then you'd only display questions that don't exist in that userQuestions table for that user.

Upvotes: 1

Jack Marchetti
Jack Marchetti

Reputation: 15754

Could you add something to the users info in the database which contains a list of answered questions?

So when that user comes back you can only show them questions which are NOT answered?

Upvotes: 1

Related Questions