Today I ran into a problem. I need to sort results of a given query by fixed column values.
Example
I have a column named status and which holds the following values 1,2,3,4
When I user normal sort method like
Order By status ASC will return 1,2,3,4
Order By status DESC will return 4,3,2,1
Suppose If I need the result to be sorted in a particular Order 3,2,4,1 then I ran into problem. Both method cannot solve my problem.
Then Googled and I found a thread in MySQL forum where Papalagi Pakeha had the same problem.
http://lists.mysql.com/mysql/209777
For that Johan Höök replied with a solution
http://lists.mysql.com/mysql/209784
Where I can user Order By Field function to get the needed result. So my query will look something like
Order By Field(status,3,2,4,1) which does the magic. I searched the whole MySQL documentation but I am unable to find the proper documentation. Who cares it works for me 😉
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
you can use order by rand() if you want to randomize the result and so on…
try this “select *,Field(status,3,2,4,1) from tbl Order By Field(status,3,2,4,1)” and you will see how it works 🙂
The function Field give the weight of the value based on the list 🙂
can anybody explain me how to get only maximum or heights value of a field.
thanks
@nayana adassuriya
MAX()
If this is red, I would seriously suggest that the owner of this site secure it..
Nice.
Example for string based custom ordering(for which I came to this article):
ORDER BY FIELD(day, ‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’)
This is very cool – you just saved me a lot of time doing my own custom pageinate method (CakePHP framework) 🙂
Bookmarked 🙂
All I have to say is THANKS!!!! for the custom order sort…. with the FIELD technique….
awesome!… thanks….
Awesome options and THANKs.. this is a good option to sort the records in ascending or descending order and have the exceptional values that is those parameters in the field will be sorted last.
Thanks to Copper and Puneet Pugalia and all the others