SELECT ... ORDER BY issue

Databases
1

Hi there,

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.

Many thanks,
Mike

2

I like your 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

3

stacked CASE conditions will do it

also, this –

ORDER BY FIELD(grade, 'Unworn', 'Excellent', 'Good', 'Average')

:slight_smile:

4

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.

5

did you happen to notice the post immediately above yours?

6

Hey r937 thanks for your post, I it the first time round, but yeah worked a treat.

Thanks again.