tags:

views:

585

answers:

7

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 :)

+1  A: 

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.

pjp
I think the point is that the desired order is not the code or ID order.
cletus
I was assuming that a table of codes exists along with code id and order id.
pjp
+6  A: 

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.

Arkain
+3  A: 

Couple options:

  1. Add OrderCode column with numerical desired order

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

Ray
+4  A: 

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
)
Blorgbeard
I agree with the "horrible hack" part ;-)
Joachim Sauer
I agree too :), and it only works with a static list, if another code was added at some point in the future, the store procedure would have to be refactored as well, to accommodate this change.
Arkain
Yes it would be an arse to maintain and not as ellegant as I'd like but this is what I'll have to do. I already suggested a
Tikeb
cont.. a sepertate table for just this thing but good ideas are often ignored unfortunatly. So when this has to be maintained I'll happily put in that the alternative suggested months ago would have been better - thanks guys
Tikeb
A: 

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.

Russ Cam
+1  A: 

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

+2  A: 

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

shantanuo