MySQL custom order (again) the output of ORDER BY column

Question

In MySQL, let's say I have a table:

 ----------------------
| Price | ProductType |
 ----------------------
8.5     | Foodstuff
8.5     | Mobile
147     | Application
2       | Mobile
8.5     | Electronic
8.5     | Mobile

In this situation, if I ORDER BY Price, ProductType, it will normally come out as:

 ----------------------
| Price | ProductType |
 ----------------------
2       | Mobile
8.5     | Electronic
8.5     | Foodstuff
8.5     | Mobile
8.5     | Mobile
147     | Application

Now the problem is:

• Can I customize the ORDER by myself? (e.g, Which one comes first, and which one comes second)

For example, how to write a MySQL Query to get a result like the following:

 ----------------------
| Price | ProductType |
 ----------------------
2       | Mobile
8.5     | Electronic <- 1st in Order
8.5     | Mobile     <- 2nd in Order
8.5     | Mobile     <- ..
8.5     | Foodstuff  <- 3rd in Order
147     | Application

Please focus on 8.5 price rows, in this sample (after the first Order By on Price column), then obviously I want to display: Electronic first, then Mobile and then Foodstuff. Is it possible in MySQL?

Answer 1

Just add a third (INT) column in your table for your custom order, name it something smart. Then you can order by that:

ORDER BY custom_order

If you still want the price to take precedence over the custom order you could do your query:

ORDER BY Price, custom_order

You just CAN'T make MySQL guess the order you want to arrange by so the alternative solution should be the one suggested by JW. where you specify the actual order in the query. However with a large table and dynamical data I would recommend my solution.

Then hopefully you have a clever administration for your database so you can easily reorder the items the way you want... I have successfully made many db administrations and like to use AJAX and jQuery Sortable for a simple drag-drop reorder.

Answer 2

use FIELD

ORDER BY Price, FIELD(ProductType,'Electronic','Mobile','Foodstuff')

• SQLFiddle Demo