MYSQL Sort by non alphanumeric

Question

I have a site that stores cooking tutorials. one of the columns in the mysql database holds either Novice, Intermediate, Advanced, Expert. I'd like to know if there was a way to write the MySQL query to order the results in the above fashion? Is this even possible?

Yes its possible to write an expression that converts those values into values that can be sorted, and to put that expression into the ORDER BY clause. There are lots of ways to do that. The most portal ANSI-compliant would be to use a CASE expression e.g. ORDER BY CASE mycol WHEN 'Novice' THEN 1 WHEN 'Intermediate' THEN 2 WHEN 'Advanced' THEN 3 WHEN 'Expert' THEN 4 ELSE 5 END. With MySQL, we have several options for shorter expressions that achieve an equivalent result.e.g. FIND_IN_SET(mycol,'Novice,Intermediate,Advanced,Expert') — spencer7593
– spencer7593, Commented Nov 15, 2017 at 21:38

Hugo Delsing · Accepted Answer · 2017-11-15 21:35:23Z

1

You can sort by INSTR if its a small table:

ORDER BY INSTR('Novice, Intermediate, Advanced, Expert', `colname`)

But this will be slow on a large table. Better to just store a numeric value from 1 to 4 and add an index on it. Then translate the number to Novice, Intermediate, Advanced, or Expert when needed. Or perhaps two columns but that could lead to differences between the numeric and textual value.

edited Nov 15, 2017 at 21:35

answered Nov 15, 2017 at 21:29

Hugo Delsing

14.3k5 gold badges50 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL Sort by non alphanumeric

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related