[SOLVED] ORDER BY (unexpected)

[bcoffin]

I've got scores in my MySQL table {A+, A, A-, B+, B, B-, ..., F}

And when I order by score, I'm getting:

 

A

A+

A-

B

B+

B-

 

etc.

 

Does anyone know any tricks to get this to order differently, so that it appears:

 

A+

A

A-

B+

B

B-

 

... and so on?

 

I know that's non-standard, but I'm sure we can figure out a trick?

 

Perhaps storing A  or something like that?

 

Ideas?

 

Thanks,

Benny

[akitchin]

you could convert these to numerical GPAs in the SELECT, and use that field for ORDERing the query.  no use storing it in another format:

 

SELECT stuff, (CASE letter_grade WHEN 'A+' THEN 4 WHEN 'A' THEN 3.7 WHEN ... ELSE THEN 0) AS numerical_grade FROM table ORDER BY numerical_grade DESC

[bcoffin]

GENIUS.

Thanks, buddy.