MYSQL custom sorting – order by field value

In this simple example I will show you a way to perform a custom sorting in MySQL. In this guide of custom sorting I am going to sort mysql rows based on the alphabetical order of the field values.

Most of the times you display MySQL result on a page based on the column in ascending or descending order. By default MySQL would sort columns based on its type i.e. numeric or string. If it is a numeric field then it would be sorted 0-9 and A-Za-Z and vice versa.

What is different in this example?

Here’s the scenario. I had a category menu loaded from a MySQL table in which parents and children were being sorted alphabetically, in ascending order.

Everything was going fine until client put a “View All” link in the menu tree and wanted to show that under each master and at the top of all slaves keeping other slaves intact i.e. sorted alphabetically.

I really wanted to use my own intellect to make it work and i was succeeded in it too. Here is the line which i discovered :) .

SELECT * FROM `$table_name` 
WHERE parent_id = '$parent_id' 
ORDER BY `text`='View All' DESC, `text` ASC

The above code worked and worked great!

Now when i had patted my back enough, decided to Google for the similar and possibly more wise and precise solution for this thing to happen (which i had already assumed to be there and it was). This magic word FIELD is a extension to ORDER BY clause and exists in the form of a MySQL function. The query line is:

SELECT * FROM `$table_name` 
WHERE parent_id = '$parent_id' 
ORDER BY FIELD( `text`,'View All') DESC, `text` ASC

gives

View All
ATV
Buttons
Cats
……

So if you decide to place ‘View None’ next right under the ‘View All’ and above other slaves sorted alphabetically just place View None’ to make the query look like:

SELECT * FROM `$table_name` 
WHERE parent_id = '$parent_id' 
ORDER BY FIELD( `text`,'View All', 'View None') DESC, `text` ASC

results in

View All
View None
ATV
Buttons
Cats
……
I hope this be a nice solution for sorting records by a custom field / value helps many.

Share on Facebook

Tweet

Follow us

Save