MySql Order By Own Values, MySql Custom “Order By”

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.

1 SELECT * FROM tbl_test
2 WHERE id IN (1,2,3,4,5)
3 ORDER BY FIELD(id, 1,3,5,4,2);
Posted in Uncategorized. Leave a Comment »

Share your thoughts & feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: