SQL Order By list of strings ?

Question

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   Phone
5   Health
6   Hot

so rather than just do a simple Order By asc/desc I'd like to order by Health, Phone, Freeze, Hot. Is this possible?

Thanks :)

Answer 1

Yes join your results to your code table and then order by code.CodeOrder

EDIT: Explaing the use of the code table...

Create a separate table of Codes (CodeId, Code, CodeOrder) and join to this and order by CodeOrder. This is nicer than doing the order by (case...) hack suggested since you can easily change the codes and the orders.

Answer 2

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   1 
2   Freeze   3
3   Phone    2
4   Hot      4

Then you can join.

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

Hope this gives you the idea.

Answer 3

Couple options:

1. Add OrderCode column with numerical desired order

2. Add a table with FK to this table ID and OrderCode

Answer 4

Here's a horrible hack:

select * from table
order by (
     case Code 
     when 'Health' then 0 
     when 'Phone' then 1
     when 'Freeze' then 2
     when 'Hot' then 3
     end
)

Answer 5

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 1
        WHEN 'Phone' THEN 2
        WHEN 'Freeze' THEN 3
        WHEN 'Hot' THEN 4
    END As OrderBy
FROM Table
ORDER BY 
    OrderBy

Or

SELECT 
    ID,
    Code
FROM Table
ORDER BY 
    CASE Code
        WHEN 'Health' THEN 1
        WHEN 'Phone' THEN 2
        WHEN 'Freeze' THEN 3
        WHEN 'Hot' THEN 4
    END

(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.

Answer 6

Hi this is a Sql Server format but I am sure you can do this in mysql as well:

SELECT ID, Code FROM x ORDER BY CASE Code WHEN 'Health' THEN 1 WHEN 'Phone' THEN 2 WHEN 'Freeze' THEN 4 WHEN 'Hot' THEN 5 ELSE 6 END ASC , Code ASC

Answer 7

select * from table order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')