I didn't just provide a solution because this looks so much like homework and the OP did not answer that one way or the other.

Here is my solution using the temp table from my previous post. It is pretty much the same as Luis's but I used a temp table instead of doing it on the fly. I agree that in 2005+ this would be a LOT easier.

select s.*, ISNULL(se.sortorder, 6) as SortOrder

from student s

left join #Sequence se on se.ClassName = s.class

join

(

select rollno, min(ISNULL(sortorder, 6)) as SortOrder

from student s

left join #Sequence se on se.ClassName = s.class

group by rollno

) g on g.rollno = s.rollno and g.SortOrder = ISNULL(se.sortorder, 6)

order by rollno, ISNULL(se.sortorder, 6)