TSQL: Extract Next Random Question On A QUIZ

DECLARE @UserID INT = 1;
DECLARE @PrevQuestionID INT = NULL;

WITH PossibleQuestions AS
(
SELECT QuestionID, Question, Answer1, Answer2, Level
FROM Questions
WHERE PrevQuestionID = @PrevQuestionID
AND QuestionID NOT IN (SELECT QuestionID FROM UserAnswers WHERE UserID = @UserID)
),
Levels AS
(
SELECT MIN(Level) AS MinLevel
FROM PossibleQuestions
)
SELECT TOP 1 QuestionID, Question, Answer1, Answer2, Level
FROM PossibleQuestions
CROSS JOIN Levels
WHERE Level = MinLevel
ORDER BY NEWID();

Scroll to Top