CODEWITHSUNDEEP
phpsql
gator
gator

Reputation: 3523

Setting atypical sorting order

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).

Upvotes: 2

Views: 70

Answers (3)

Kevin Bowersox
Kevin Bowersox

Reputation: 94429

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

Upvotes: 6

bobs
bobs

Reputation: 22184

You might consider another table that manages product condition:

CREATE TABLE product_condition
(
product_condition_id INT,
grade char(2),
grade_sequence int
)

INSERT INTO product_condition VALUES (1, 'NM', 1)
INSERT INTO product_condition VALUES (2, 'M', 2)
INSERT INTO product_condition VALUES (3, 'E', 3)
INSERT INTO product_condition VALUES (4, 'G', 4)
INSERT INTO product_condition VALUES (5, 'P', 5)

Replace the actual grade column in your product table with the product_condition_id.

SELECT *
FROM product p
JOIN product_condition pc on p.product_condition_id = p.product_condition_id
ORDER BY grade_sequence

I suspect that grade values are fairly stable, but with the table you can make changes more easily.

Upvotes: 1

agim
agim

Reputation: 1841

Why don't you simply use i.e. numbers in the database? I mean you can use 1 for near mint, 2 for mint, 3 for excellent and so forth.

Upvotes: 2

Related Questions