Specific ordering SQL Command

Question

Say I have a table similar to:

ID    Name
1     Test
2     Contest
3     Fittest
4     Testament

Is there a MySQL query I could use with ordering to allow it to display a specific word first?

For example, users are searching for the word "Test". I have a statement similar to "SELECT * FROM table WHERE NAME LIKE '%Test%'". Could I display results to show things that START with Test begin first followed by everything else, while everything is still in alphabetical order.

So output would be:

Test
Testament
Contested
Fittest

Thanks.

Answer 1

This will put your words that begin with Test at the top, and sort those words plus the remainder of the list in alphabetical order..

SELECT * FROM mytable 
ORDER BY CASE WHEN name LIKE 'test%' THEN 0 ELSE 1 END ASC, name ASC