MYSQL custom sorting – order by field value

Yesterday i had a somewhat new (for me ) and different kind of requirement while displaying MySQL result on a page. I had a category menu in which masters and slaves were sorted alphabetically and 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.

One thought on “MYSQL custom sorting – order by field value

Leave a Reply

Your email address will not be published. Required fields are marked *