CODEWITHSUNDEEP
mysqlsqlsql-order-byfield
Tak
Tak

Reputation: 167

How to use "ORDER BY FIELD" to sort the result of a SQL

If there is a data shown as below;

id  cnt_stamp    
1  999 
2  3
3  9
4  3 
5  1000
6  30

If an input is (4, 1, 2, 3) in this order, I would like to get only (3, 999, 3, 9). To achieve this, I created a SQL

SELECT `cnt_stamp`
FROM `stm_events`
ORDER BY FIELD(`id`, 4, 1, 2, 3);

But it returns (1000, 30, 3, 999, 3, 9) instead. How should I fix my SQL to achieve my goal? Thank you for taking your time.

Upvotes: 0

Views: 267

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

FIELD will assign NULL to any non matching id, and nulls sort first by default in MySQL. If you don't want to see non matching items at all you may just add a WHERE clause:

SELECT cnt_stamp
FROM stm_events
WHERE id IN (1, 2, 3, 4)
ORDER BY FIELD(id, 4, 1, 2, 3);

If you want to see all your data, with non matching id values at the end, then reverse the order of the field list and sort descending:

SELECT cnt_stamp
FROM stm_events
ORDER BY FIELD(id, 3, 2, 1, 4) DESC;

Demo

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65208

Use COALESCE function :

SELECT `cnt_stamp` 
  FROM `stm_events` 
 ORDER BY COALESCE(`id`,FIELD(`id`,4,1,2,3));

SQL Fiddle Demo

Upvotes: 1

Related Questions