CODEWITHSUNDEEP
mysqlsql-order-by
user1400718
user1400718

Reputation:

Order by VARCHAR value in MySQL

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?

Upvotes: 0

Views: 196

Answers (3)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

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

Upvotes: 0

Zagor23
Zagor23

Reputation: 1963

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

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

For your case ::

Select status from table group by status

will give the result you need

Upvotes: 0

Related Questions