Randomizing Query Results EXCEPT Last Answer

[SurgeProto]

OK, so this is a bit tricky, but basically I have a survey module where multiple choice questions have a certain amount of preset answers. For example purposes, lets say this question has 5 answers: Yankees, Red Sox, Dodgers, Giants, Cubs

I query for my answers like this:

$ansquery = "SELECT * FROM answers WHERE qid=$qid";
if (($survey_randomization == 1) && ($ignore_randomization == 0))
{
	$ansquery .= " ORDER BY RAND()";
}
else
{
	$ansquery .= " ORDER BY sortorder, answer";
}

Basically, if randomization is on, I randomize the answers, otherwise I order by sortorder.

Now, I have a specialty case where I want the first few answers to be random but I want the LAST answer to be static. Using the example above, Yankees, Red Sox, Dodgers, and Giants and be randomly shuffled but Cubs MUST be the last answer every time.

I don't think I can do this with one query can I? The other problem is, the number of answers is random, so I don't necessarily know how many exist for a question. I'm guessing I would have to maybe append the arrays together?

Any insight on this would be very greatly appreciated!

[NogDog]

In your example, is there something in the database that indicates that "Cubs" should be the one that is sorted to the end? If so, you could use that for an additional sort criteria.

SELECT *, IF(something = 'foo', 1, 0) AS sort1, RAND() AS sort2 . . . ORDER BY sort1, sort2

[SurgeProto]

Hey, Thanks for the reply.

Unfortunately no, all the answers are random, so the only way I'll know an item is the "last answer" is that the 'sortorder' field will be the highest in the group.