MYSQL union or order by?

Question

I am trying to figure out how to grab information from my database in the order that I want to. For example, I have the names below in my database:

Matt
Jimmy
Craig
Jenny
Sue

I want to get them out in this order:

Sue
Craig
Jimmy
Matt
Jenny

How would I do that using MYSQL?

Answer 1

Use:

ORDER BY CASE t.name 
           WHEN 'Sue' THEN 1 
           WHEN 'Craig' THEN 2
           WHEN 'Jimmy' THEN 3
           WHEN 'Matt' THEN 4
           WHEN 'Jenny' THEN 5
           ELSE 6
          END

It's using a CASE statement to assign an arbitrary value based on the value for ordering.

Answer 2

Sure, you can do it like this (if I am understanding your requirements correctly):

order by field(t.name, 'Jenny', 'Matt', 'Jimmy', 'Craig', 'Sue') desc

EDIT:

Actually, if you want to handle a generic case with a number of already-known values of the column and the number of unknown values, the common case would be to make the known values appear in the beginning of the resultset (in the predefined order) and the rest after them. For this, you should add the predefined values in the reverse order to the field function argument list and make the order descending.

This way 'Sue' will go first (field returns 5), 'Craig' - second (4 returned) and so on up to 'Jenny'. For the values not in the argument list, field returns 0. So they will go to the end of the resultset.

Reference: FIELD