While going through a problem, I got a situation where I wanted to have a result set in a sorting order which is defined by me. Not ORDER BY ASC, not ORDER BY DESC, not ORDER BY RAND(), but ORDER BY FIELD i.e. the values which are defined by me, which could be random as well, but not ORDER BY RAND().
MySql Custom Order By Example
For example, if you have a result set of a table, containing the names of students, and you have set them as order by student names, and now you want the same ordering format of student names, in another table, which only only contains ids of the student. One easy way could be to make a join with the master table, containing the students names, and use simple Order By student name format, but there could be another way out, where you wont be needing to join the other table, just for the sake of ordering. You can use following type of query, where you already have the ordering student ids, i.e. you already know that which id will come first, and next, and so on.
ORDER BY FIELD
Following is the table tbl_test, where field id will be containing the student ids, through which we will be fetching 5 student ids, and we already know their sorting order, that which id will come first, and next and so on.
SELECT * FROM tbl_test WHERE id IN (1,2,3,4,5) ORDER BY FIELD(id, 1,3,5,4,2);