CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
Roshan Shaw
Roshan Shaw

Reputation: 45

Select specified items from rows at first priority mySql

I have a table users

id | Name |
1    John 
2    Dwayne
3    Daniel
4    Ronaldo
5    Messi
6    Gareth
7    Leonardo
8    Brad

If i have id's in order of (7,5,1,3) and i want to select limit 6 then the expected output should be as following :-

id  Name 
7 | Leonardo
5 | Messi
1 | John 
3 | Daniel
2 | Dwayne
4 | Ronaldo

What i did previously was this but did not get the expected output . Help!!

"select * from users ORDER BY FIELD(user_id,7,5,1,3) LIMIT 6"

Upvotes: 1

Views: 226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your query is close. It just doesn't take into account all the other user ids. Try this:

ORDER BY FIELD(user_id,7,5,1,3) > 0 desc,
         FIELD(user_id,7,5,1,3)
LIMIT 6;

The problem with your version is that field() returns 0 when there is no match. And the 0s will be ordered first.

Upvotes: 1

Related Questions