设置非典型排序顺序

Question

I'm working with a database where end users can upload photos of their collectibles and other effects, and under one of the criteria they can add entries is the condition of the product (near mint, mint, excellent, etc). If I want to sort these entries by condition, it will sort it alphabetically, but this doesn't correspond to the actual sorted-by-condition. For example, E (excellent) will appear before M (mint), when excellent is worse than mint.

How can I assign values to these letters in my database so when sorted they will appear in an order that is not alphabetic, but by actual grade (M > NM > E > G > P).

Answer 1

Add a case statement to the order by clause. Here is some pseudo sql:

SELECT * FROM COLLECTIBLES
ORDER BY CASE condition
  WHEN 'EXCELLENT' THEN 1
  WHEN 'MINT' THEN 2
  WHEN 'NEAR MINT' THEN 3
  ELSE 4
END

Working Example: http://sqlfiddle.com/#!2/328c2/1