Ordering by specific field values


/ Published in: MySQL
Save to your folder(s)



Copy this code and paste it in your HTML
  1. SELECT * FROM fruit
  2. ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
  3. +----------+--------+---------------------+
  4. | fruit_id | name | variety |
  5. +----------+--------+---------------------+
  6. | 11 | Banana | Burro |
  7. | 12 | Banana | Cavendish |
  8. | 10 | Banana | Plantain |
  9. | 6 | Apple | Cox's Orange Pippin |
  10. | 7 | Apple | Granny Smith |
  11. | 1 | Apple | Red Delicious |
  12. | 8 | Pear | Anjou |
  13. | 4 | Pear | Bartlett |
  14. | 2 | Pear | Comice |
  15. | 5 | Orange | Blood |
  16. | 3 | Orange | Navel |
  17. | 9 | Orange | Valencia |
  18. +----------+--------+---------------------+
  19.  
  20. SELECT * FROM fruit
  21. ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
  22. +----------+--------+---------------------+
  23. | fruit_id | name | variety |
  24. +----------+--------+---------------------+
  25. | 6 | Apple | Cox's Orange Pippin |
  26. | 7 | Apple | Granny Smith |
  27. | 1 | Apple | Red Delicious |
  28. | 11 | Banana | Burro |
  29. | 12 | Banana | Cavendish |
  30. | 10 | Banana | Plantain |
  31. | 8 | Pear | Anjou |
  32. | 4 | Pear | Bartlett |
  33. | 5 | Orange | Blood |
  34. | 2 | Pear | Comice |
  35. | 3 | Orange | Navel |
  36. | 9 | Orange | Valencia |
  37. +----------+--------+---------------------+
  38.  
  39. SELECT * FROM fruit
  40. ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
  41. +----------+--------+---------------------+
  42. | fruit_id | name | variety |
  43. +----------+--------+---------------------+
  44. | 11 | Banana | Burro |
  45. | 12 | Banana | Cavendish |
  46. | 10 | Banana | Plantain |
  47. | 6 | Apple | Cox's Orange Pippin |
  48. | 7 | Apple | Granny Smith |
  49. | 1 | Apple | Red Delicious |
  50. | 5 | Orange | Blood |
  51. | 3 | Orange | Navel |
  52. | 9 | Orange | Valencia |
  53. | 8 | Pear | Anjou |
  54. | 4 | Pear | Bartlett |
  55. | 2 | Pear | Comice |
  56. +----------+--------+---------------------+

URL: http://www.electrictoolbox.com/mysql-order-specific-field-values/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.