Advanced Search

Ordering by specific field values

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

Expand | Embed | Plain Text

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.