Sql Order By List Of Strings?

I wish to do a select on a table and order the results by a certain keyword or list of keywords. For example I have a table like so: ID Code 1 Health 2 Freeze 3 Phone 4 Ph

Solution 1:

Try using this:

select*fromtableorderby FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')

Solution 2:

Here's a horrible hack:

select*fromtableorderby (
     case Code 
     when'Health'then0when'Phone'then1when'Freeze'then2when'Hot'then3end
)

Solution 3:

You can join with the Keywords table, and include a sequence column, and ORDER BY Keyword.Sequence.

Example your keywords table looks like this:

ID  Code     Sequence
1   Health   12   Freeze   33   Phone    24   Hot      4

Then you can join.

SELECT *
FROM   MyTable INNER JOIN
          Keywords ON Keywords.ID = MyTable.KeywordID
ORDERBY Keywords.Sequence

Hope this gives you the idea.

Solution 4:

Is this just a one off ORDER BY or something that you're going to want to do often and on more values than specified here?

The order that you have given is arbitrary, therefore an identifier needs to be given to achieve what you want

SELECT 
    ID,
    Code,
    CASE Code
        WHEN'Health' THEN 1WHEN'Phone' THEN 2WHEN'Freeze' THEN 3WHEN'Hot' THEN 4ENDAs OrderBy
FROM Table
ORDERBY 
    OrderBy

Or

SELECT 
    ID,
    Code
FROMTableORDERBYCASE Code
        WHEN'Health'THEN1WHEN'Phone'THEN2WHEN'Freeze'THEN3WHEN'Hot'THEN4END

(I'm not familiar with MySQL but the above would work in SQL Server. The syntax for MySQL won't be too different)

If you're likely to want to do this often, then create an OrderBy column on the table or create an OrderBy table with a FK link to this table and specify an OrderBy numerical field in that.

Solution 5:

Nowadays MySQL has a function called find_in_set()

Use it like this:

select*fromtableorderby find_in_set(Code,'Health','Phone','Freeze','Hot')