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.
I like your solution.
one alternative is to use the MySQL case and assign it a number as part of your SQL.
select CASE grade when ‘Unworn’ then 1
when ‘Excellent’ then 2
when ‘Good’ then 3
when ‘Average’ then 4
stacked CASE conditions will do it
also, this –
ORDER BY FIELD(grade, 'Unworn', 'Excellent', 'Good', 'Average')
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.
did you happen to notice the post immediately above yours?
Hey r937 thanks for your post, I it the first time round, but yeah worked a treat.