CODEWITHSUNDEEP
sqlsql-server
TRose
TRose

Reputation: 1738

How to order results randomly, but with exceptions?

I am creating Quiz and Test system.

In the database, we have a list of questions, and a list of possible answers.

These are all supposed to be returned in random order, so students cannot copy from each others' work.

The one exception is when questions have a possible answer like "All Of The Above", in which case we give it a boolean value, [AlignBottom]. If [AlignBottom] is 1, we must force this answer to appear last under a given question, with the rest of the answers still loaded randomly before it.

This is what I tried first just to see what would happen. Obviously it didn't work:

SELECT [AnswerID]
      ,[QuestionID]
      ,[AnswerValue]
      ,[IsCorrect]
      ,[AlignBottom]
  FROM [dbo].[Test-Answers]
  WHERE QuestionID = 4
  ORDER BY AlignBottom ASC, NewID()

And I couldn't find this specific sort of solution on SO.

Help?

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Based on your description, your query should work:

ORDER BY AlignBottom ASC, NewID()

Upvotes: 4

Max Zolotenko
Max Zolotenko

Reputation: 1132

Replace ORDER clause with next:

Order by case when [AlignBottom] = 1 then -1 else abs(checksum(NewID())) end desc

Upvotes: 1

Related Questions