12

For example I have in the table EMPLOYEE:

(code, name)
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

if you do: (select * from EMPLOYEE) you will get:

(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

if you do: (select * from EMPLOYEE where code in (1,3,2,4) you will get:

(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )

How to get it in the order of CSV values in the IN clause, as is?

(1, 'Jimmy')
(3, 'Michelle')
(2, 'Albert')
(4, 'Felix' )
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
CRISHK Corporation
  • 2,948
  • 6
  • 37
  • 52
  • 1
    possible duplicate of [Sort by order of values in a select statement "in" clause in mysql](http://stackoverflow.com/questions/2185029/sort-by-order-of-values-in-a-select-statement-in-clause-in-mysql) – JohnFx Feb 13 '11 at 22:17
  • Also a duplicate of http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list – JohnFx Feb 13 '11 at 22:18
  • Also a duplicate of http://stackoverflow.com/questions/1372864/mysql-in-clause-and-the-returned-record-set-order – JohnFx Feb 13 '11 at 22:18
  • 1
    Also a duplicate of http://stackoverflow.com/questions/3399591/sql-sort-order-by-the-order-specified-in-the-query – JohnFx Feb 13 '11 at 22:19
  • Also a duplicate of http://stackoverflow.com/questions/2245936/order-resultset-based-on-where-in-clause-data – JohnFx Feb 13 '11 at 22:19
  • @JohnFX: I knew one of them had to be mine. The tagging on them could use updating to be easier to find. – OMG Ponies Feb 13 '11 at 22:39

2 Answers2

19

Use the FIND_IN_SET function:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY FIND_IN_SET(e.code, '1,3,2,4')

Or use a CASE statement:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY CASE e.code
           WHEN 1 THEN 1 
           WHEN 3 THEN 2
           WHEN 2 THEN 3
           WHEN 4 THEN 4
         END
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
1

The general solution to this problem, retaining the order based on your input (CSV) file, is to add an AUTO_INCREMENT column to your table and order based on that. You probably will never display it as part of your query, but you can order on it to get the original order in your input file, after the import.

Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • 1
    What if the order is different between viewers (IE: you and I)? – OMG Ponies Feb 13 '11 at 20:46
  • 1
    @Ponies, well this assumes a single import of the entire table and a single order based on file input. I am basing my answer on his coupling of the question with a particular CSV (input file) order. – Michael Goldshteyn Feb 13 '11 at 20:48
  • Other answer assumes perfect knowledge of CSV file. Why use a database any more since you have to reload the CSV file to get the correct order? Obviously you have to record the information somewhere in the database or you already know everything so why even query it? If the order is different between different viewers ??? (does the CSV file change positions). If ordering is that important and actually means something it should be recorded in the DB. – nate c Feb 13 '11 at 21:24