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?

score 65 · Accepted Answer · edited Jan 26 '14 at 20:03

65

Try using this:

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

edited Jan 26 '14 at 20:03

hichris123

10,145
15
56
70

answered Aug 10 '09 at 12:27

shantanuo

31,689
78
245
403

3

This is the proper solution as per the docs(comments section). A good explanation can be found [here](http://www.electrictoolbox.com/mysql-order-specific-field-values/) – kapad Sep 03 '13 at 13:09
12

Be aware that the FIELD() function is specific to MySQL and may not work on other SQL database servers. – orrd May 09 '17 at 21:17
This solution doesn't work in MS SQL 2019. – Warf Aug 19 '21 at 06:41

score 15 · Answer 2 · answered Aug 07 '09 at 10:36

15

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
)

answered Aug 07 '09 at 10:36

Blorgbeard

101,031
48
228
272

10

I agree with the "horrible hack" part ;-) – Joachim Sauer Aug 07 '09 at 10:40
1

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. – Jesper Fyhr Knudsen Aug 07 '09 at 10:45
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 Aug 07 '09 at 10:47
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 Aug 07 '09 at 10:48
I just confirmed this works in SQLite while doing a little lazy hacking. – Henry Rivera Oct 31 '18 at 03:03

Jesper Fyhr Knudsen · Answer 3 · 2009-08-07T10:39:20.997

13

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.

edited Aug 07 '09 at 10:39

answered Aug 07 '09 at 10:31

Jesper Fyhr Knudsen

7,802
2
35
46

score 5 · Answer 4 · edited Nov 03 '14 at 18:50

5

Nowadays MySQL has a function called find_in_set()

Use it like this:

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

edited Nov 03 '14 at 18:50

Eric Leschinski

146,994
96
417
335

answered Jul 01 '11 at 10:04

watchout

91
1
2

score 4 · Answer 5 · answered Aug 07 '09 at 10:37

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.

score 4 · Answer 6 · edited Feb 09 '14 at 12:32

4

Hi this is a SQL Server query 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

edited Feb 09 '14 at 12:32

TLama

75,147
17
214
392

answered Aug 07 '09 at 10:49

pjp · Answer 7 · 2009-08-07T11:33:02.213

3

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.

edited Aug 07 '09 at 11:33

answered Aug 07 '09 at 10:29

pjp

17,039
6
33
58

I think the point is that the desired order is not the code or ID order. – cletus Aug 07 '09 at 10:30
1

I was assuming that a table of codes exists along with code id and order id. – pjp Aug 07 '09 at 11:26

score 3 · Answer 8 · answered Aug 07 '09 at 10:33

3

Couple options:

Add OrderCode column with numerical desired order
Add a table with FK to this table ID and OrderCode

answered Aug 07 '09 at 10:33

Ray

1,585
9
10

SQL Order By list of strings?

8 Answers8

Linked