127

I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.

INPUT:

SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');

OUTPUT:

|   id   |   name  |
^--------^---------^
|   5    |   B     |
|   6    |   B     |
|   1    |   D     |
|   15   |   E     |
|   17   |   E     |
|   9    |   C     |
|   18   |   C     |

Any ideas?

Donald Duck
  • 8,409
  • 22
  • 75
  • 99
Matt
  • 3,778
  • 9
  • 35
  • 36

6 Answers6

271
SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

Ayman Hourieh
  • 132,184
  • 23
  • 144
  • 116
  • 9
    @Vladimir - yes, it is MySQL-specific. The question has the mysql tag. – Ayman Hourieh Jun 06 '09 at 01:10
  • Great, replacement for Oracle's "decode" function after DB switch. – Martin Lyne Nov 15 '12 at 13:36
  • 12
    Careful. Any unknown property value (not in the list) will take precedence over the known values, i.e. `FIELD(letter, 'A', 'C')`, the list will first return entries with B letter first (assuming a set of records with `A | B | C` values). To avoid that, inverse the list and use DESC, i.e. `FIELD(letter, 'C', 'A') DESC`. – Gajus Mar 30 '17 at 17:42
  • How do I achieve this in SQL server. – user123456 Mar 31 '19 at 04:32
31

Another option from here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

select * 
from tablename 
order by priority='High' DESC, priority='Medium' DESC, priority='Low" DESC;

So in your case (untested) would be 

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY name = 'B', name = 'A', name = 'D', name =  'E', name = 'C';

Depending what you're doing I've found it a bit quirky but always got it to work after playing with it a bit.

ccellar
  • 10,326
  • 2
  • 38
  • 56
joedevon
  • 2,649
  • 4
  • 28
  • 43
  • This may be better than using the field() function as another answer suggested because using field() will preclude index usage, but it has a chance to use an index using this method (not sure how well it might use the index, though) – ʞɔıu Jun 06 '09 at 15:28
  • This is great but I needed to specify `DESC` as the direction after each part. The equality check would have a value of `1` and all the rest would be `0`, so I would need the one that passed the check, to come up first. – The Unknown Dev Sep 16 '20 at 21:29
  • 1
    We found this method to be _much_ slower than using `FIELD()`. We may have been using it incorrectly or on too big a dataset (~22,000 returned records) but just make sure you do your own benchmarking. – Joshua Pinter Sep 23 '20 at 17:10
4

Try something like 

... ORDER BY (CASE NAME WHEN 'B' THEN 0 WHEN 'A' THEN 1 WHEN ...
Vladimir Dyuzhev
  • 18,130
  • 10
  • 48
  • 62
3

May be this can help someone (p_CustomerId is passed in SP): 

SELECT CompanyAccountId, CompanyName
FROM account
LEFT JOIN customer where CompanyAccountId = customer.AccountId
GROUP BY CompanyAccountId
ORDER BY CASE WHEN CompanyAccountId IN (SELECT AccountId 
                                          FROM customer
                                          WHERE customerid= p_CustomerId) 
                 THEN 0
                 ELSE 1
          END, CompanyName;

Description: I want to show the account list. Here i am passing a customer id in sp. Now it will list the account names with accounts linked to that customers are shown at top followed by other accounts in alphabetical order.

ccellar
  • 10,326
  • 2
  • 38
  • 56
Hun
  • 31
  • 1
2

You need another column (numeric) in your table, in which you specify the sort order. The IN clause doesn't work this way.

B - 1
A - 2
D - 3
E - 4
C - 5
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
0

just use 

order by INSTR( ',B,C,D,A,' ,  concat(',' , `field`, ',' ) )

avoid the situation like 

 INSTR('1,2,3,11' ,`field`)

will end with unordered result row : 1 and 11 alternant

PercyQQ
  • 1
  • 1