CODEWITHSUNDEEP
phpmysql
mohamad bagheri
mohamad bagheri

Reputation: 499

mysql - order by field not working properly

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

Upvotes: 11

Views: 7060

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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

Upvotes: 17

Rene Korss
Rene Korss

Reputation: 5484

Try with DESC

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

It seems your id order is important. Reverse numbers to get correct result

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

Upvotes: 2

MD SHAHIDUL ISLAM
MD SHAHIDUL ISLAM

Reputation: 14523

Not tested but you can try

SELECT id,
        (
            CASE 
                WHEN id = '3' THEN 0 
                WHEN id = '4' THEN 1 
                WHEN id = '1' THEN 2
            END
        ) as rank 
        FROM prod  
        ORDER BY rank
        LIMIT 20;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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
;

Upvotes: 4

Related Questions