[FIXED] mysql - order by field not working properly

Issue

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

Solution

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

Answered By - Gordon Linoff