mysql - order by field not working properly

Question

suppose I've a database , the table contains rows with ides from 1 to 20 .

i want to return 3 rows with id 3,4,1 first and then return the other rows . this is my code :

    SELECT id
FROM prod
ORDER BY field( id, 3, 4, 1 ) 
LIMIT 20

this is the result of this code :

id
13 
17 
16 
15 
 7 
 6 
 5 
 2 
 3 
 4 
 1

strangely the 3 rows that I need to come first are showing at the end ,

How can I bring these 3 rows to the top of the list ?

Thanks

Answer 1

The other way is to use case-when and giving each id an order value

select * from prod 
order by 
case 
  when id = 3 then 0  
  when id=4 then 1 
  when id=1 then 2 
  else 3 
end,id 
limit 20
;

Answer 2

You can use DESC:

SELECT id
FROM prod
ORDER BY field( id, 3, 4, 1 ) DESC
LIMIT 20

The issue is that MySQL puts NULL values first when you do an ascending order by.

If you actually want the rows in the order 3, 4, 1, then reverse them in the field statement:

SELECT id
FROM prod
ORDER BY field( id, 1, 4, 3 ) DESC
LIMIT 20

Or, if you wanted to be fancy:

ORDER BY - field( id, 3, 4, 1 ) DESC