MySQL order by text

Question

I know how I can sort by id or number like

$getTicket = $sql->query("SELECT * FROM `ticket` 
                          WHERE `user`='$user->name' 
                          ORDER BY `id` DESC");

I have Status in the ticket table, and in that I have 3 things:

Answered
Unanswered
Done

I want to sort it in this way:

Unanswered
Answered
Done

Is there a way to do this?

dupe of [Ordering by specific field value first](https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first) — underscore_d, Jul 07 '20 at 15:35

score 6 · Accepted Answer · edited Oct 03 '15 at 14:28

6

Do simple as-

ORDER BY FIELD(Status, 'Unanswered', 'Answered', 'Done')

edited Oct 03 '15 at 14:28

Gordon Linoff

1,242,037
58
646
786

answered Oct 03 '15 at 14:26

Rohit Kumar

1,948
1
11
16

score 4 · Answer 2 · answered Oct 03 '15 at 14:24

You can generally use case

SELECT * FROM `ticket` 
WHERE `user` = '$user->name' 
ORDER BY case when Status = 'Unanswered' then 1
              when Status = 'Answered' then 2
              else 3
         end

or MySQL specific find_in_set

SELECT * FROM `ticket` 
WHERE `user` = '$user->name' 
ORDER BY find_in_set(Status, 'Unanswered,Answered,Done')

MySQL order by text

2 Answers2