Order by VARCHAR value in MySQL

Question

I have a table with 2 columns

id  status

1   SUBMIT
2   CANCEL
3   UPDATE
4   CANCEL
5   SUBMIT
6   UPDATE

If I do:

select * from table

Can I order the results in the order I want using MySQL?
ASC would be CANCEL,SUBMIT,UPDATE and DESC the reverse. What if I want a different order like SUBMIT,CANCEL,UPDATE? Is it possible?

this question http://stackoverflow.com/questions/5857036/mysql-concecutive-order-positions might be helpful — Ivan Solntsev, Dec 04 '12 at 13:23

score 2 · Accepted Answer · answered Dec 04 '12 at 13:14

2

If I understood correctly, this is what you need:

SELECT * FROM `Table` ORDER BY FIELD(status, 'SUBMIT', 'CANCEL', 'UPDATE');

You can change the order of colum values to correspond your demand.

Here's the test: http://sqlfiddle.com/#!2/d1885/3

answered Dec 04 '12 at 13:14

Zagor23

1,953
12
14

score 0 · Answer 2 · answered Dec 04 '12 at 13:12

0

For your case ::

Select status from table group by status

will give the result you need

answered Dec 04 '12 at 13:12

Sashi Kant

13,277
9
44
71

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

When you search for mysql custom sort order you will find lots of examples. One of them is order by case

select *
from mytable
order by case status when 'SUBMIT' then 1
                     when 'CANCEL' then 2
                     when 'UPDATE' then 3
         end

SQL Fiddle

edited May 23 '17 at 12:27

Community

1
1

answered Dec 04 '12 at 13:18

Olaf Dietsche

72,253
8
102
198

Order by VARCHAR value in MySQL

3 Answers3