I am working on a watch-reseller’s site. Each product has a grade ‘Unworn’, ‘Excellent’, ‘Good’, ‘Average’.
I need to order by Grade, but obviously MySQL will order alphabetically. So ‘Unworn’, ‘Good’, ‘Excellent’, ‘Average’, or vise versa. Not good. The obvious solution would be to have a grades table and then order the results by grade id, with an INNER JOIN… but before I change my database design, I was wondering if anyone could suggesta more elegant solution.
one alternative is to use the MySQL case and assign it a number as part of your SQL.
e.g.
select CASE grade when ‘Unworn’ then 1
when ‘Excellent’ then 2
when ‘Good’ then 3
when ‘Average’ then 4
end
from Product
If you use order by clause then it would sort it by alphabetical order, your solution which you specified of giving grade_id would work , other wise go for procedure.