mickyginger — 2012-05-08T07:00:38-04:00 — #1
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.
jurn — 2012-05-08T10:38:46-04:00 — #2
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
r937 — 2012-05-08T11:05:40-04:00 — #3
stacked CASE conditions will do it
also, this --
ORDER BY FIELD(grade, 'Unworn', 'Excellent', 'Good', 'Average')
timu123 — 2012-05-09T06:01:22-04:00 — #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.
r937 — 2012-05-09T06:56:12-04:00 — #5
did you happen to notice the post immediately above yours?
mickyginger — 2012-05-31T06:33:40-04:00 — #6
Hey r937 thanks for your post, I it the first time round, but yeah worked a treat.
mittineague — 2014-09-23T03:24:50-04:00 — #7
This topic is now archived. It is frozen and cannot be changed in any way.