user987346
user987346

Reputation:

Custom sorting on varchar field in mysql is possible?

I have one field named "priority" with type varchar(20) in my table, which can have these four values: show stopper, high, medium, low

Now, when I sort this with query "Select priority from issues order by priority" it results me: 1. High 2. Low 3. Medium 4. Show stopper

But I need this on following order: 1. Show stopper 2. High 3. Medium 4. Low

Please suggest me what can do in above written query to fetch the result in this order? I know if I will change the varchar to enum, problem will be solved. But I can not change the field type due to some project concept restrictioans, so please tell me some other solution. Thanks in advance.

Upvotes: 1

Views: 423

Answers (1)

user557846
user557846

Reputation:

             ...
             ORDER BY CASE priority WHEN 'show stopper' THEN 1
             WHEN 'high' THEN 2
             WHEN 'medium' THEN 3
             WHEN 'low' THEN 4
             END

or

             ORDER BY FIELD(priority,'show stopper', 'high', 'medium', 'low');

Upvotes: 4

Related Questions