CODEWITHSUNDEEP
mysql
saad saad
saad saad

Reputation: 39

MySQL order by certain values even if not existing in the table

I have a Table is looking like this 

 id         name
-----      ------
1          Mark
2          Mike
3          John
4          Mike
5          Mike
6          John
7          Mark

Notice that there isn't name Paul in the table so when ordering by name I want it to be like this ('Mark', 'Paul', 'Mike','John')

        id         name
       -----      ------
        1          Mark
        7          Mark
       Null        Paul
        2          Mike
        4          Mike
        5          Mike
        3          John
        6          John

I already have tried the field method and it is removing Paul because it is not in the table

ORDER BY Field(name, 'Mark', 'Paul', 'Mike','John')

Upvotes: 0

Views: 27

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

you can't order by a value that not exist so you should add a proper set of data for ordering 

select a.id, t.name 
from (
   select 'Mark' name, 1 my_order 
   union 
   select  'Paul', 2 
   union  
   select 'Mike', 3 
   union  
   select 'John', 4 ) t 
 left join my_table a   on a.name = t.name  
 order by t.my_order

Instead of a subquery with union you could use a proper utility table

Upvotes: 3

Related Questions