using the FIELD function to sort specific order

Language: SQL

Posted by: Shelane French

Added: Jan 24, 2020 9:43 PM

Tags: no tags

You can do a tricky thing WITH a FIELD() FUNCTION IN an ORDER BY TO GET elements IN a certain ORDER.
The FIELD() FUNCTION RETURNS the INDEX OF the FIRST element IN the subsequent elements.
So a TYPE OF "invoice" will RETURN 5 AND "home physical" will RETURN 1.
You need TO USE DESC because IF NONE OF the list IS found it will RETURN 0, which you presumably want LAST.
SELECT * FROM `addresses` WHERE fk_orgid = 1234 ORDER BY FIELD(TYPE, "home physical", "home postal", "work physical", "work postal", "invoice") DESC;