Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Shelane French
Added: Jan 24, 2020 9:43 PM
Views: 8
Tags: no tags
  1. You can do a tricky thing WITH a FIELD() FUNCTION IN an ORDER BY TO GET elements IN a certain ORDER.
  2. The FIELD() FUNCTION RETURNS the INDEX OF the FIRST element IN the subsequent elements.
  3. So a TYPE OF "invoice" will RETURN 5 AND "home physical" will RETURN 1.
  4. You need TO USE DESC because IF NONE OF the list IS found it will RETURN 0, which you presumably want LAST.
  5.  
  6. SELECT * FROM `addresses` WHERE fk_orgid = 1234 ORDER BY FIELD(TYPE, "home physical", "home postal", "work physical", "work postal", "invoice") DESC;