Specific order (sql)

Question

I have got a model Pupil, which has got field score. I need to make a list with strange rule: order by score (DESC) but (!) all scores that more than 100 should ordered as zero:

Pupil.all( :order => 'score DESC' ...?)
100
86
34
21
6
3
1
0
143
125
354
0
456
0
0

I can order it using ruby, but I need sql

Aslo I can create additional field in db for storing data like
new_score = score > 100 ? 0 : score
but I think we can make sql without it

Answer 1

you can do order by 'score > 100' (order the booleans), then order by score. All those with score > 100 will be pre-ordered by the boolean, and the sub-order will order the other scores after the >100 ones.

I hope this was clear enough and what you were looking for :)

Answer 2

SELECT * FROM pupil order by score > 100 , score desc